Reducing the SQL Server database and logs smallest possible without data loss ?

Hi All,

I'm running the below database in older physical box:

Microsoft SQL Server 2012 - 11.0.5343.0 (X64) 
	May  4 2015 19:11:32 
	Copyright (c) Microsoft Corporation
	Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

Open in new window


and planning to migrate it to different box using offline backup method (cold backup). Hence I need to shrink the SQL server files to reduce the manual copy time.

Without impacting or configuring the database size from the application using the SQL server, how can I shrink the SQL .MDF and .LDF file size ? I need some steps and guidance in this.

Is there any impact on the application when I shrink those databases files to the smallest possible ?

Thanks in advance.
LVL 9
Senior IT System EngineerIT ProfessionalAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
the technical part is documented:
https://msdn.microsoft.com/en-us/library/ms190757.aspx
using the sql server dbcc shrinkfile command, you can do that (note: it's not a command line tool, but really a sql command, to be executed being connected in sql to that db. there is also the possibility to do that in the GUI, if you feel safer doing it that way
0
Senior IT System EngineerIT ProfessionalAuthor Commented:
Ah I see,

I prefer to use the GUI, is it using:

Right click at the DB Tasks > Shrink > Database
Right click at the DB Tasks > Shrink > Files (I assume this is the log files) ?

Is there any specific order to do it ?

DB first or log first ?
0
Senior IT System EngineerIT ProfessionalAuthor Commented:
Guy,

What settings should I select for the DB files shrink (.MDF) below ?
Database
What settings should I select for the Log files shrink (.LDF) below ?
Logs
0
Has Powershell sent you back into the Stone Age?

If managing Active Directory using Windows Powershell® is making you feel like you stepped back in time, you are not alone.  For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
shrink database will try to do the "shrink files" for all the files...
I prefer the "shrink files", as there I can see better what files to effectively shrink.

the checkbox to reorganize the file space should make sure that any free space in the middle of the file is "moved" towards the end of the file, as only there the "free space" can be released.
however, for especially large files that are highly "fragmented internally", and with low free space, this will not work, and is then not needed anyway.
=> the only way to get that "fixed" is to add new files to the filegroup(s), or usually better, add a new filegroup, move all the objects to that new filegroup, shrink then the original files, and move the objects back...
also, you may consider to anyhow add files to your filegroups if the files are getting so big, but this is a completely different story altogether.

in regards to the log files, you should make sure you run a full backup or a transaction log backup just before. a reorganization may work, but not always.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Senior IT System EngineerIT ProfessionalAuthor Commented:
Cool,

I have just did select File Type: Logs and then selecting Release unused space and it went so quick, I can now see significant disk space released.

Can i do:

 File Type: Data and then selecting Release unused space again for all SQL server database.
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you cannot do that "for all databases" unless you start scripting the commands (using dbcc shrinkfile)
0
Senior IT System EngineerIT ProfessionalAuthor Commented:
Ah yes, but if I can do it manually one by one then it is not a problem right ?

I need to shrink both DB & logs to the smallest possible.
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
no problem to do it one by one (in the gui)
0
Senior IT System EngineerIT ProfessionalAuthor Commented:
Thanks !
0
Senior IT System EngineerIT ProfessionalAuthor Commented:
Hi Guy,

When I shrink the databse from GUI, I got this:

TITLE: Microsoft SQL Server Management Studio
------------------------------

Shrink failed for Database 'CSR-DB'.  (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=11.0.5058.0+((SQL11_PCU_Main).140514-1820+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Shrink+Database&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

Lock request time out period exceeded.
Lock request time out period exceeded. (Microsoft SQL Server, Error: 1222)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=11.00.5343&EvtSrc=MSSQLServer&EvtID=1222&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
This bascially means you still have users on the db, maybe "yourself"
this is sometimes tricky using the GUI, when you have some tables open in edit more or so, or simply an open transaction on the db.
you need to make sure everybody having some long/locking transcations gets logged off
0
Senior IT System EngineerIT ProfessionalAuthor Commented:
Already did, the application services are all stopped.

I cannot even detach it and cannot Turn offline the database ?

TITLE: Microsoft SQL Server Management Studio
------------------------------

Drop all active database connections failed for Server 'PRODDB01'.  (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=11.0.5058.0+((SQL11_PCU_Main).140514-1820+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Drop+all+active+database+connections+Server&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

Only user processes can be killed. (Microsoft SQL Server, Error: 6107)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=11.00.5343&EvtSrc=MSSQLServer&EvtID=6107&LinkId=20476

------------------------------
BUTTONS:
OK
------------------------------
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
as I said, it may be "yourself".
in the GUI, if you expanded the database, or have an open sql command sheet open to the db (use <database>, it's already that.
you can check in the active sessions if it's really you.
make sure that your session is linked to another db, and then just right-click on the database in the GUI to start the operations
other than that, I would see replication, or sql agent jobs (backups?) running?
1
Senior IT System EngineerIT ProfessionalAuthor Commented:
Yes, you are awesome :-)

After I close the SSMS and then re open without opening the script window, I can perform the Database Shrink succesfully.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.