how to shrink sql server express [Resolved]

Posted by Artisingh30 under Sql Server on 8/28/2010 | Points: 10 | Views : 3789 | Status : [Member] | Replies : 2

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

DO you have any solution for this-
Need to shrink sql server express database because it's taking up too much room on the hard drive and also in processes sqlserver.exe is using over 1GB of memory. One table had 745,000 records and was 641MBs in size so I deleted 375,000 to leave just 370,000 records. Then I right clicked on the database and clicked on Tasks>>shrink database and it seemed to work fine but the the table is no 633MBs so it has hardly changed at all. There are no other tables of any great significance, the next biggest table is 118MBs and then there are two tables of 50MBs each. Can anyone help?

[Resolved]

Reply | Reply with Attachment

Alert Moderator

Responses

Posted by: PandianS on: 8/28/2010 [Member] [MVP] Silver | Points: 50

0	Hi You can solve your problem by following the activity 1. Shrinking the Database and Log file is the best option (But not frequent). You can schedule the shrink activity in Off-peak hours of the database access. 2. Defragment the Tables / Indexes timely maner by scheduling job in Off-Peak hours 3. Isolate the TempDB database from your Database physical location. ie: If your Database and TempDB Database are there in D:\ Drive then, Try to move your Database to some other drive E:\ or F:\ 4. Try to avoid using NVARCHAR, NCHAR like National Datatypes in Tables. so you can avoid unnecessary of memory usage. 5. If possible, Try to archive the outdated records into some other database in Different Server / Different location in the same server. 6. Try to implement Partition on Tables with multiple filegroups for frequently increasing transaction tables. ie: Filegroups should be placed in different Physical location (C:\, D:\, E:\,...) 7. If you not implemented any Mirroring / Log Shipping / Replication / Transaction Backup on Server then, Try to maintain the Database's Recovery Model as "SIMPLE". Insteadof "FULL" Cheers Cheers www.SQLServerbuddy.blogspot.com iLink Multitech Solutions Artisingh30, if this helps please login to Mark As Answer. \| Alert Moderator

Hi

You can solve your problem by following the activity

1. Shrinking the Database and Log file is the best option (But not frequent). You can schedule the shrink activity in Off-peak hours of the database access.

2. Defragment the Tables / Indexes timely maner by scheduling job in Off-Peak hours

3. Isolate the TempDB database from your Database physical location.
ie: If your Database and TempDB Database are there in D:\ Drive then, Try to move your Database to some other drive E:\ or F:\

4. Try to avoid using NVARCHAR, NCHAR like National Datatypes in Tables. so you can avoid unnecessary of memory usage.

5. If possible, Try to archive the outdated records into some other database in Different Server / Different location in the same server.

6. Try to implement Partition on Tables with multiple filegroups for frequently increasing transaction tables.
ie: Filegroups should be placed in different Physical location (C:\, D:\, E:\,...)

7. If you not implemented any Mirroring / Log Shipping / Replication / Transaction Backup on Server then, Try to maintain the Database's Recovery Model as "SIMPLE". Insteadof "FULL"

Cheers

Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

Artisingh30, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Artisingh30 on: 8/29/2010 [Member] Starter | Points: 25

0	thanks a lot Artisingh30, if this helps please login to Mark As Answer. \| Alert Moderator

Latest Posts