Solved Shrinking mdf files on SQL SERVER

September 15, 2015 at 07:21:47
Specs: Windows 64
Hi All,
I am back looking for advise. I want to make sure that my file sizes are being monitored ok and that i have everything set up ok.

My MDF files for my report server db are quite large like 37GB.
I have checked and i have used space of 36251.69 MB and unused spave of 2.81MB
I back up my ldf files and they are only .23MB

I would like to know if i can do anything to descrese the size of this db.
I thought i could decrese in increments but when i tried, nothing changed. I restarted the server and still there was no difference.
I tried to set aa free space of 1000MB after the shrink , but that obviously didn't work.

i don't know if i'm doing something incorrectly when i set up my mdf and ldf files.

Can anyone please advise?

See More: Shrinking mdf files on SQL SERVER

Report •

September 15, 2015 at 10:30:49
The .ldf file is a transaction log, so you wouldn't expect it to get too big. If you've stored 36251.69 MB of data in your database there's not much that can be done to reduce the size of the database files. But that's a lot of data - what are you storing in it?

message edited by ijack

Report •

September 22, 2015 at 09:17:04
✔ Best Answer
You may try both variant of restoration:

First powerful resources

Guide working via SQL Management Studio

1. Download and install MS SQL Server Management Studio Express for the version of the MS SQL database installed with the product.
Note: Installation of Management Studio Express 2008 requires installation of the Microsoft Web Platform first, after which Management Studio Express 2008 will follow.

2. After installation, open the application from Start > Programs > Microsoft SQL Server 2005/2008 > SQL Server Management Studio Express.

3. Log into the database using Windows Authentication

4. Expand Databases, then right-click on wt_sched and choose "Properties."

5. Select "Options" and scroll to the bottom in the pane on the right.

6. Change "Restrict Access" to "SINGLE_USER."

7. Select OK and repeat the above steps for the wtMaster section of the database.

8. Select "New Query" from the toolbar, and in the query pane that opens, paste in the following commands:

dbcc checkdb('wtMaster',REPAIR_REBUILD)
dbcc checkdb('wt_sched',REPAIR_REBUILD)

9. Select the "Execute" button to repair the database.

10. Perform the steps outlined in 4-6 above and change the databases back to "MULTI_USER" from "SINGLE_USER."

Note: Changing the database back to MULTI_USER mode may require logging out and logging back in using SQL Server Management Studio Express in order to change both parts back to their original settings. Attempting to make these changes may result in an error saying there are too many connections. Closing Management Studio and then re-opening it again will allow these changes to take effect.

Report •

June 17, 2016 at 00:25:49
I found another helpful post that shows how to shrink SQL .mdf File

Report •

Related Solutions

Ask Question