| Author |
Topic |
|
MaverickUK
Yak Posting Veteran
89 Posts |
 Posted - 2004-01-21 : 05:58:14
|
Hey guysSadly our installation of SQL Server is on a very small C drive, so I'm in the process of moving, sp_detach_db & sp_attach_db-ing the files to our D drive.Whilst doing this I've noticed that we have some small databases (smaller than 50MB) that don't store much permiment data, but have a lot of SELECT/UPDATE/INSERT/DELETE activity. This has meant the transaction log for these database has grown extremely (on our scale) large, bigger than 1GB in some cases.If I set the transaction logs not to grow, would this cause a problem? Would SQL Server just other right older transaction log data, with newer data in this scenero?Please advise guys  |
|
|
smccreadie
Aged Yak Warrior
505 Posts |
Posted - 2004-01-21 : 06:19:35
|
| Do you backup your transaction logs frequently? The purpose of transaction logs is to keep a history of activity between backups. |
 |
|
|
MaverickUK
Yak Posting Veteran
89 Posts |
Posted - 2004-01-21 : 06:25:32
|
| I believe the entire drive is backup up onto DLT everynight. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2004-01-21 : 07:52:50
|
| Backing up the drive outside of SQL Server will do nothing to clear the transaction log, and it's not a safe situation to be in if you have to restore data. You have to use the BACKUP LOG command in Transact-SQL. It's also better to use SQL Server's BACKUP command to perform database backups. You can put the backup files on another machine and have that machine backed up to tape. This not only gives you redundant backups but it makes it much easier to restore than having to scan a tape. |
|
|
|
MaverickUK
Yak Posting Veteran
89 Posts |
Posted - 2004-01-21 : 08:01:40
|
| So by performing a BACKUP LOG command, this transactional data will be backed up and then the file size of the log file will shrink?Is this correct? |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2004-01-21 : 08:06:05
|
| It won't shrink, but it will be cleared of active transactions. THEN you can shrink the log using DBCC SHRINKFILE. |
|
|
|
MaverickUK
Yak Posting Veteran
89 Posts |
Posted - 2004-01-21 : 08:11:24
|
| Aha, I see, now it makes sense why DBCC SHRINKFILE wasn't effecting the transaction log before :)Thank you |
|
|
|
MaverickUK
Yak Posting Veteran
89 Posts |
Posted - 2004-01-21 : 09:42:20
|
| Sorry to ask more, but I'm having some problems performing a backup.I'd like to perform the backup to another machine on our network, which has ample space to the data.This is the command I'm usingBACKUP DATABASE TxtSystem TO DISK = '\\Hqw2kfs2\fs2-d\TxtSystem.dat' WITH INITHowever, I get the following errorServer: Msg 3201, Level 16, State 1, Line 1Cannot open backup device '\\Hqw2kfs2\fs2-d\TxtSystem.dat'. Device error or device off-line. See the SQL Server error log for more details.Server: Msg 3013, Level 16, State 1, Line 1BACKUP DATABASE is terminating abnormally.I've done as much research as I can, but everything suggests this should work. I thought perhaps it might be due to a permissions issue, but I ran this through QA whilst logged in with my account, which has admin permissions.Any ideas? |
|
|
|
Nazim
A custom title
1408 Posts |
Posted - 2004-01-21 : 10:31:03
|
| Add your n/w drive as a device ( you have to do it only once)sp_addumpdevice 'disk', 'NetworkDriveBack','\\Hqw2kfs2\fs2-d\TxtSystem.dat'BACKUP DATABASE TxtSystem TO NetworkDriveBack WITH INITHTH-------------------------What lies behind you and what lies ahead of you are small matters compared to what lies within you.-Ralph Waldo Emerson |
|
|
|
MaverickUK
Yak Posting Veteran
89 Posts |
Posted - 2004-01-21 : 10:50:31
|
| I actually tried that style of syntax first, same problem sadly :/Server: Msg 3201, Level 16, State 1, Line 9Cannot open backup device 'TxtSystemBK'. Device error or device off-line. See the SQL Server error log for more details.Server: Msg 3013, Level 16, State 1, Line 9BACKUP DATABASE is terminating abnormally. |
|
|
|
spireite
Starting Member
12 Posts |
Posted - 2004-01-21 : 11:09:16
|
| Are you running SQLServer under LocalSystem? It is SQLServer that needs access rights to remote drives. LocalSystem doesn't give you this. |
|
|
|
MaverickUK
Yak Posting Veteran
89 Posts |
Posted - 2004-01-22 : 08:04:54
|
| I'm not sure... could you tell me how to check this please? |
|
|
|
monkeybite
Posting Yak Master
152 Posts |
Posted - 2004-01-22 : 09:46:58
|
quote:
Originally posted by MaverickUK
I'm not sure... could you tell me how to check this please?
Right-click on the server in Enterprise Manager, then go to the Security tab. Look in the Startup Service account area, you have two choices there, System Account, or another specified account.You can also look in the Services applet in the control panel.~ monkey |
|
|
|
MaverickUK
Yak Posting Veteran
89 Posts |
Posted - 2004-01-22 : 11:09:14
|
| Aha, it's using the system account.I'm looking at the list of NT accounts on that server, but I can't see one that SQL Server would obviously use. Which NT account does it use? |
|
|
|
|
SQL Server database space usage