Solved

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

Posted on 2016-08-22
14
41 Views
Last Modified: 2016-08-23
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.
0
Comment
  • 8
  • 6
14 Comments
 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 500 total points
ID: 41766377
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
 
LVL 7

Author Comment

by:Senior IT System Engineer
ID: 41766379
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
 
LVL 7

Author Comment

by:Senior IT System Engineer
ID: 41766381
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
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 41766389
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
 
LVL 7

Author Comment

by:Senior IT System Engineer
ID: 41766405
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 41766412
you cannot do that "for all databases" unless you start scripting the commands (using dbcc shrinkfile)
0
 
LVL 7

Author Comment

by:Senior IT System Engineer
ID: 41766427
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 41766442
no problem to do it one by one (in the gui)
0
 
LVL 7

Author Closing Comment

by:Senior IT System Engineer
ID: 41766445
Thanks !
0
 
LVL 7

Author Comment

by:Senior IT System Engineer
ID: 41766505
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 41766507
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
 
LVL 7

Author Comment

by:Senior IT System Engineer
ID: 41766519
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 41766524
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
 
LVL 7

Author Comment

by:Senior IT System Engineer
ID: 41766525
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

Featured Post

Why do Marketing keep bothering you?

Is your marketing department constantly asking for new email signature updates? Are they requesting a different design for every department? Do they need yet another banner added? Don’t let it get you down! There is an easy way to manage all of these requests...

Join & Write a Comment

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to shrink a transaction log file down to a reasonable size.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now