• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 352
  • Last Modified:

sql server scripts needed

does any body has script that runs weekly to shrink ldf files
0
aatishpatel
Asked:
aatishpatel
  • 7
  • 6
  • 2
8 Solutions
 
chapmandewCommented:
you have been given the scripts you need to do this in your previous posts.  you have EVERYTHING that you need already.
0
 
tangchunfengCommented:
define a backup job in sql server agent
when backup log file , you have the option to shrink the ldf


0
 
chapmandewCommented:
one of the same links I posted for you before....

http://blogs.techrepublic.com.com/datacenter/?p=448
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
tangchunfengCommented:
0
 
aatishpatelAuthor Commented:
ok thanx
0
 
aatishpatelAuthor Commented:
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
 
chapmandewCommented:
are you doing log backups?
0
 
aatishpatelAuthor Commented:
i am doing a full database backup every week and every other day a differential backup
0
 
chapmandewCommented:
no log backups?
0
 
aatishpatelAuthor Commented:
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
 
chapmandewCommented:
>>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
 
aatishpatelAuthor Commented:
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
 
chapmandewCommented:
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
 
aatishpatelAuthor Commented:
but my manager said that every sunday evening just do shrinkfile. because on saturday we run full backup. that's all he said.
0
 
aatishpatelAuthor Commented:
thanx all
0
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.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

  • 7
  • 6
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now