Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL server logs backup / reduction


SQL server logs backup / reduction

Author
Message
Steve Jones
Steve Jones
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: Administrators
Points: 36070 Visits: 18736
aayushmail007 (4/22/2013)
Thanks Steve and Gila.
if this is organization data what would you prefer ?

My first requirement will be to have space in drive
2nd recover when needed


Those are not the priorities I'd have. That may lead to you not recovering at all.

What's the drive size, what's the data size, what's the log size. How much data import/change per day?

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Ice007
Ice007
SSC Rookie
SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)

Group: General Forum Members
Points: 36 Visits: 22
C drive : 200gb

sql log: 140 gb
sql data : 30 gb
etc : 10-20gb

space left : less than 5gb

logs increments 1.5- 2gb a day
Steve Jones
Steve Jones
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: Administrators
Points: 36070 Visits: 18736
If you're loading 1-2 GB a day in the log, I'd shrink the log to 5GB. Space is relatively cheap, and this gives you pad.

You don't need a 140GB log, and likely it grew because you didn't run log backups. Switch the db to simple mode, shrink the log. Schedule (don't run, schedule) a full backup every day.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Ice007
Ice007
SSC Rookie
SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)

Group: General Forum Members
Points: 36 Visits: 22
first I have to run log backup by taking it offline and then use backup option in SQL mgmt studio.

the challenge here is that the sql server is on remote desktop and I will be taking backup in another mapped drive,
but I cannot see that drive when I select disk . Only two options are C and D .
Cannot locate mapped drive
Steve Jones
Steve Jones
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: Administrators
Points: 36070 Visits: 18736
You don't need to take anything offline to run a backup. If you take the database offline, you can't run a backup in SSMS.

As far as the mapped drives, the server doesn't see drives you've mapped in your session. The service account for the server has it's own mapped drives.

What I would say here is run the full backup. use a UNC path. Then switch to simple mode. This will generate a checkpoint and the log chain is broken. You should be able to shrink the log then.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Ice007
Ice007
SSC Rookie
SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)

Group: General Forum Members
Points: 36 Visits: 22
I tried couple of UNC but I am not getting it correct :

I have an x drive mapped to it , what should be the correct UNC?
Steve Jones
Steve Jones
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: Administrators
Points: 36070 Visits: 18736
You don't need to map a drive. The service account for SQL Server logs into Windows. It can't see your drive mappings when you've logged in with RDP.

Instead, run the backup like this:

Backup database mydb to disk='\\backupserver\share\myshare\mybackup.bak'



Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Ice007
Ice007
SSC Rookie
SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)

Group: General Forum Members
Points: 36 Visits: 22
error

Msg 3201, Level 16, State 1, Line 1
Cannot open backup device '\\X\analyitcs\test_UNC.BAK'. Operating system error 53(The network path was not found.).
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.


I went to services and restarted the SQL server and now analytics its in a recovery mode.
Hope I have not screwed it up?
Steve Jones
Steve Jones
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: Administrators
Points: 36070 Visits: 18736
Your service account may not have rights to that path. Not all accounts will. You'll have to determine if that's the case.

If the db is recovering, you will have to look in the error log to find out why.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47198 Visits: 44367
Ice007 (4/22/2013)
Cannot open backup device '\\X\analyitcs\test_UNC.BAK'. Operating system error 53(The network path was not found.).


As the error message says, the network path does not exist. Nor would I expect it to unless you have a server named "X"
\\<server name>\<share name>\<path>


Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search