September 26, 2011 at 7:34 am
Hi every body i am not familer in SQL server the below query executed on my production server.
It will causes any affect on production server ?
ALTER DATABASE dbname set recovery simple
go
alter database bctop set single_user with rollback immediate
go
dbcc shrinkfile(logfile,55000)
go
alter database dbname set multi_user
go
September 26, 2011 at 7:40 am
It shrinks the log to 55 000 MB.
You lose any hability to do point in time recovery untill you redo a full backup.
No users can login and you kick everybody out untill it finished running.
This is not something you should be running in production unless you had a major blow up that overgrew the tlog files by a heck of a lot.
Is this scheduled to run periodically? because it just shouldn't!
September 26, 2011 at 7:56 am
Just it took 30sec to complete. I am feared it goes to any problematic ?
I did say this thing to my manager
Please give me a suggestion :
can i say this thing to my manager or not ?
September 26, 2011 at 7:59 am
ssk0507 (9/26/2011)
Just it took 30sec to complete. I am feared it goes to any problematic ?I did say this thing to my manager
Please give me a suggestion :
can i say this thing to my manager or not ?
You HAVE TO tell him.
You also have to take a full backup right NOW.
September 26, 2011 at 8:25 am
Actually we have two servers A and B. Every day A backup will be restoring in B server for report purpose
yesterday restore job has failed beacuse space issue(there was no sufficient space).
after executing the above query still my database recovery model in simple in B server but that query accidentally executed.
as you told for take the backup on A server there was no sufficent space?
September 26, 2011 at 8:38 am
The restore makes a 100% exact copy of the db at the time of the end of the backup.
So if the prod db on server have has 100 GB of files (data + logs) then you need to have 100GB of free space on server B.
You can't go around that. You need to either reduce the size of the db on the server A or make more room on server B.
September 26, 2011 at 8:48 am
Thank for your help
September 26, 2011 at 8:53 am
Have you thought of implementing Log Shipping?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
September 26, 2011 at 8:54 am
Perry Whittle (9/26/2011)
Have you thought of implementing Log Shipping?
Is that your prefference for ssrs server over replication?? I hate having to disconnect the users every x minutes to restore the next log.
P.S. You still have to be able to do that first restore (obviously) 😉
September 26, 2011 at 10:39 am
Ninja's_RGR'us (9/26/2011)
Is that your prefference for ssrs server over replication??
why is replication yours then 😉
For most users log shipping provides excellent report offloading. I dont understand your point about the restore could you explain
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
September 26, 2011 at 10:44 am
Perry Whittle (9/26/2011)
Ninja's_RGR'us (9/26/2011)
Is that your prefference for ssrs server over replication??why is replication yours then 😉
For most users log shipping provides excellent report offloading. I dont understand your point about the restore could you explain
I've never set either up.
I was under the impression that during the log shipping when the log restore occurs that all connections need to be severed.
In our case here it would seem problematic (hence my theorical "best" choice of replication).
September 26, 2011 at 11:22 am
yes, you are correct. If the database is in standby mode then users have to be disconnected to restore the logs.
The point is, there isn't necessarily an absolute requirement to restore each log every 15 mins (assuming a log backup every 15 mins). All scenarios are different. It's a lot easier to setup and maintain than replication, that's what is so sweet about it.
For a lot of reporting scenarios i have encountered, it's been acceptable for the data to be a period of maybe a couple of hours or more behind production. You can vary the copy and restore jobs to run when required. You might backup every 15 mins to give yourself a fine degree of PIT restore, copy the files every 2 hours and restore every 4 hours. Users only get disconnected every 4 hours.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
September 26, 2011 at 11:25 am
Perry Whittle (9/26/2011)
yes, you are correct. If the database is in standby mode then users have to be disconnected to restore the logs.The point is, there isn't necessarily an absolute requirement to restore each log every 15 mins (assuming a log backup every 15 mins). All scenarios are different. It's a lot easier to setup and maintain than replication, that's what is so sweet about it.
For a lot of reporting scenarios i have encountered, it's been acceptable for the data to be a period of maybe a couple of hours or more behind production. You can vary the copy and restore jobs to run when required. You might backup every 15 mins to give yourself a fine degree of PIT restore, copy the files every 2 hours and restore every 4 hours. Users only get disconnected every 4 hours.
I C. This wouldn't fly here as most reports need to be with live data!
September 26, 2011 at 11:38 am
yes, like i say every system is different. Here's the other thing though, if you are backing up, copying and restoring every 15 mins the disconnects and the restores are usually extremely quick. I'd venture to say most users wouldn't even notice 😉
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
September 26, 2011 at 1:52 pm
Perry Whittle (9/26/2011)
yes, like i say every system is different. Here's the other thing though, if you are backing up, copying and restoring every 15 mins the disconnects and the restores are usually extremely quick. I'd venture to say most users wouldn't even notice 😉
Ya but there's still a disconnect, that's the issue.
Most users are not computer savy. I'd be getting calls that my cr@p is not working. Or worse, not getting call when there's an actual issue.
This is a low usage environement but we still get 500 reports / hour on average.
Most need 10-30 seconds to process. So as you can see it's not a matter of if we kill a god connection, but only when!
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply