Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

sql server scripts needed

Posted on 2009-05-18
15
Medium Priority
?
347 Views
Last Modified: 2012-05-07
does any body has script that runs weekly to shrink ldf files
0
Comment
Question by:aatishpatel
  • 7
  • 6
  • 2
15 Comments
 
LVL 60

Assisted Solution

by:chapmandew
chapmandew earned 1140 total points
ID: 24411548
you have been given the scripts you need to do this in your previous posts.  you have EVERYTHING that you need already.
0
 
LVL 6

Assisted Solution

by:tangchunfeng
tangchunfeng earned 360 total points
ID: 24411569
define a backup job in sql server agent
when backup log file , you have the option to shrink the ldf


0
 
LVL 60

Assisted Solution

by:chapmandew
chapmandew earned 1140 total points
ID: 24411584
one of the same links I posted for you before....

http://blogs.techrepublic.com.com/datacenter/?p=448
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 6

Assisted Solution

by:tangchunfeng
tangchunfeng earned 360 total points
ID: 24411622
0
 

Author Comment

by:aatishpatel
ID: 24411709
ok thanx
0
 

Author Comment

by:aatishpatel
ID: 24412342
ok so
I have a problem shrinking the log file. The command that I use is
DBCC SHRINKFILE (IBM_log, 50)
But in result pane it shows:----  DBCC execution completed. If DBCC printed error messages, contact your system administrator. [.        And  in
Message pane it shows :------- [SQLSTATE 01000] (Message 2528)  Cannot shrink file '2' in database 'P002' to 1310720 pages as it only contains 859656 pages. [SQLSTATE 01000] (Message 7993)  Cannot shrink log file 2 (NextGen_Log) because all logical log files are in use.
 And one more thing my log files are stored in L drive L:\logs\. In some databases I see that there is only one file ibm   but in some databases there are two files ld_1  and ld_2. So I do not understand why there are two files. Can somebody explain me what are these two files?  
And I have created weekly shrink log task:- the steps are as below please verify and let me know if its correct.
And they run as sa as a owner and under master database.
USE P001
DBCC SHRINKFILE (NextGen_Log, 51250)
GO

USE P002
DBCC SHRINKFILE (NextGen_Log, 51250)
GO

USE P003
DBCC SHRINKFILE (NextGen_Log, 51250)
GO

USE NGProd
DBCC SHRINKFILE (NextGen_Log, 51250)
GO

Or somebody have some different shrink log idea for weekly basis lt me know please.
0
 
LVL 60

Assisted Solution

by:chapmandew
chapmandew earned 1140 total points
ID: 24412413
are you doing log backups?
0
 

Author Comment

by:aatishpatel
ID: 24412498
i am doing a full database backup every week and every other day a differential backup
0
 
LVL 60

Accepted Solution

by:
chapmandew earned 1140 total points
ID: 24412574
no log backups?
0
 

Author Comment

by:aatishpatel
ID: 24412691
but full should have that right?
  and one more thing the above steps had problem i did some modifications like just the size that you see is just assumable. but let me know if the rest is ok

USE P001
 
ALTER DATABASE [P001] SET RECOVERY SIMPLE
DBCC SHRINKFILE (NextGen_Log, 10240)
ALTER DATABASE [P001] SET RECOVERY FULL

GO

USE P002
ALTER DATABASE [P002] SET RECOVERY SIMPLE
DBCC SHRINKFILE (NextGen_Log, 10240)
ALTER DATABASE [P002] SET RECOVERY FULL
GO

USE P003
ALTER DATABASE [P003] SET RECOVERY SIMPLE
DBCC SHRINKFILE (NextGen_Log, 10240)
ALTER DATABASE [P003] SET RECOVERY FULL
GO

USE NGProd
ALTER DATABASE [NGProd] SET RECOVERY SIMPLE
DBCC SHRINKFILE (NextGen_Log, 10240)
ALTER DATABASE [NGProd] SET RECOVERY FULL
GO
0
 
LVL 60

Assisted Solution

by:chapmandew
chapmandew earned 1140 total points
ID: 24412716
>>but full should have that right?

No, you obviously didn't read the articles I gave you.  Just set your databases to Simple (like you're doing in the script) and DO NOT move them back to FULL...there is no reason to.
0
 

Author Comment

by:aatishpatel
ID: 24412927
actually i have to make them full again, because if i had to do simple i would not do shrinkfile. and this is a production database and that is why full recovery is a must for that. now what do you suggest, are the scripts ok.
0
 
LVL 60

Assisted Solution

by:chapmandew
chapmandew earned 1140 total points
ID: 24412967
the scripts are fine..but being ina  full recovery mode DOESN'T DO ANYTHING unless you're doing log backups....nothing whatsoever.  that is why I suggested you keep it in the simple mode.  The only advantage of FULL is that you can recover to a point in time...but you'll never be able to do that if you don't make log backups.
0
 

Author Comment

by:aatishpatel
ID: 24413251
but my manager said that every sunday evening just do shrinkfile. because on saturday we run full backup. that's all he said.
0
 

Author Closing Comment

by:aatishpatel
ID: 31582572
thanx all
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

926 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