| Author |
Topic |
|
kannipraveen
Starting Member
13 Posts |
 Posted - 2003-07-25 : 07:36:35
|
| Hi, I have Backup my database (SQL Server 2000) for Testing. Every Time I conduct a Load Test,I Will restore my DataBase.I am Monitoring Performance Counters by Using PERFMON.When Ever I restore all the Performance counters related to SQL Server are not availiable for monitoring.I need To restart the entire System once again to get back the counters.I restarted SQL server, but of no use. Tell me a solution so that i should not restart my system(Database Server) and want my perfmon intact when i restore the database. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-07-25 : 13:24:17
|
| This sounds very strange. How are you performing the backups and restores? Please explain this in detail so that we can determine what is going on. What service pack are you running for SQL 2k?Tara |
 |
|
|
kannipraveen
Starting Member
13 Posts |
Posted - 2003-07-26 : 01:08:50
|
| I am Using Service Pack 3 for Sql Server 2000.I Back up database by right clicking on database using mouse, after all required data for testing is fetched into the DB. For every load test I perform , I Restore the database as I do for backup. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-07-28 : 12:41:50
|
| Try backing up and restoring the database through Query Analyzer:BACKUP DATABASE DBNameGoesHereTO DISK = 'C:\temp\Somefilename.bak'WITH INITRESTORE DATABASE DBNameGoesHereFROM DISK = 'C:\temp\Somefilename.bak'WITH REPLACETara |
|
|
|
kannipraveen
Starting Member
13 Posts |
Posted - 2003-07-31 : 00:07:48
|
| What is the difference by using Query Analyzer and using windows gui? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-07-31 : 00:52:03
|
| This difference is that you know exactly what command is running. Enterprise Manager is also a resource hog and should be used as little as possible. If you accidentally click an option in EM, you could get undesireable results, such as the database being in standby mode. If you could explain exactly what you do in EM for the backup and restore, explain it screen by screen, then we might be able to help you.Tara |
|
|
|
kannipraveen
Starting Member
13 Posts |
Posted - 2003-07-31 : 01:15:21
|
| I open EM and select the database, for which I want to take backup. Right click on db and select all task, select Back up database. 1)I will give the destination file name to take back up. In options, I select only remove inactive entries from trans log. In general i select backup database complete & overwrite as append to media. and click on ok. back up is complete.2.I conduct a load test for 500 users and then do restore of db To make sure all the connection are freed I restart the sql server, then try to restore. 3.To Restore, Right click on db and select all task, select Restore database.select restore as database, click on properties after selecting the db which i want to restore, verify the db to be restored. In Options i select force restore over existing db & leave database operational.Then click on okMy database is restored. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-07-31 : 12:32:25
|
| Well it seems that you are doing everything correctly. Although instead of restarting the service to ensure that all connections have been dropped, you could just run ALTER DATABASE and rollback everything that way you don't have to wait.What does the load test involve? Have you tried reinstalling SQL Server yet to fix this problem? You're already restoring the database, so why not reinstall it to see if that fixes the problem. There are a bunch of MS knowledge base articles that have fixes for the missing performance counters, so you might want to do a search there to see what fits your environment.Tara |
|
|
|
kannipraveen
Starting Member
13 Posts |
Posted - 2003-08-01 : 01:26:26
|
| Load testing is a performance test that subjects the target-of-test to varying workloads to measure and evaluate the performance behaviors and abilities of the target-of-test to continue to function properly under these different workloads. This test is done to test the PORTAL or Website when 500 users are hitting the same site, like 500 people accessing www.hotmail.com at any given point of time. The goal of load testing is to determine and ensure that the system functions properly beyond the expected maximum workload. Additionally, load testing evaluates the performance characteristics, such as response times, transaction rates, and other time-sensitive issues. By using Alter Database I get into deep trouble of missing my data.The main issue I want is, to restore db without losing my perfmon counters and most important is not to waste my time restarting system. By reinstalling sql I am wasting more time which is worst |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-08-01 : 12:21:57
|
quote:
Originally posted by kannipraveen
By using Alter Database I get into deep trouble of missing my data.
Actually, it is the same thing as restarting the service as you are doing. Both will cause ROLLBACKs. ALTER DATABASE is much faster though.[quote]Originally posted by kannipraveen
The main issue I want is, to restore db without losing my perfmon counters and most important is not to waste my time restarting system. By reinstalling sql I am wasting more time which is worst[/b]Each time that you reboot your system, you are losing time. Add up all of those minutes and you're way past the amount of time it would take to reinstall the software. If you aren't comfortable with this, I would open up a support case with MS. They typically are able to resolve these performance counters quickly. I would search their knowledge base articles first though.Tara |
|
|
|
shsmonteiro
Constraint Violating Yak Guru
290 Posts |
|
|
kannipraveen
Starting Member
13 Posts |
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2003-08-06 : 07:31:08
|
quote:
Originally posted by kannipraveenThe main issue I want is, to restore db without losing my perfmon counters and most important is not to waste my time restarting system. By reinstalling sql I am wasting more time which is worst[/b]
By typing in bold giant letters and being rude to the people who are helping you for free you are wasting their time.Which is MUCH "worst"Be polite or people won't help you at all. If a link doesn't help, explain why. Nobody here is paid to help you with something you won't figure out for yourself.Damian |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-08-06 : 07:35:02
|
| I'm wondering if it would've taken you over a week to reinstall SQL Server. Because that's how long you've had this thread going, and it appears all our suggestions don't work for you. Of course you can't be bothered to try the very first suggestion someone made.So much for not wasting more time.BTW, your time is no more important that anyone else's here, those people who are trying to help you without being paid or compensated, who do it out of their own generosity. |
|
|
|
kannipraveen
Starting Member
13 Posts |
Posted - 2003-08-06 : 07:40:50
|
| I used bold not to make a issue , but to stress that what my problem really is.I am facing the problem even after reinstalling sql server. |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-08-06 : 09:38:24
|
| SCRUBI don't even know which quote to put out there for you to re-read...Brett8-)SELECT POST=NewId() |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-08-06 : 12:45:34
|
quote:
Originally posted by kannipraveen
I used bold not to make a issue , but to stress that what my problem really is.I am facing the problem even after reinstalling sql server.
Well, I'm at a loss as I think most people are too about this problem. If you really want to fix it, I sugggest calling Microsoft. I've called them before about missing Performance Counters and they were able to fix the problem rather quickly.Tara |
|
|
|
shsmonteiro
Constraint Violating Yak Guru
290 Posts |
Posted - 2003-08-06 : 20:40:00
|
| Had you checked all that is mentioned in that link? I an provide you with others, but all I can find is about permission or some program running at same time when you started SQL Server and it tryed to load the counters DLL, (for example Perfmon).Sérgio MonteiroTrust in no Oracle |
|
|
|
kannipraveen
Starting Member
13 Posts |
Posted - 2003-08-07 : 00:32:16
|
quote:
Originally posted by kannipraveen
The link provided http://support.microsoft.com/?id=152513 does not answer to my problem.
The above link speaks about perfmon not having counters. But in my case I have counters but only missing counters are SQL counters. I tried even doing the same mentioned in the above article but of no use. Counters are not listed in the local sql machine itself. I Get this problem only when I restart SQL server before restoring. The same problem gets repeated even after reinstalling sql serverI have contacted Microsoft, waiting for reply from them. |
|
|
|
|
SQL Restore