Im supporting a client that has an AS400 (DB2 database) accounting system and has recently upgraded from SQL Server 7 to 2000. He runs all his queries through SQL Server to DB2 becuase the interface and reporting is much easier.
He has found that some of the same queries run through SQl Server 7 then through SQL Server 2000 yield different row counts. A query run directly on AS400 or through SQL Server 7 returns the correct number of rows, while the same query run through SQL Server 2000 will return a count considerably less...
1,475,234 though SQL Server 7 and 2,045 through SQL Server 2000!
Does anyone one know of any changes in the openquery structure between SQL 7 & 2000 or any changes in AS400 interaction that may account for this dataloss in SQL Server 2000?I vote for the miracle thing...
Where does the data reside.
What do you mean "run on".
How is connectivity established.
And I would use the word "perceived" in your statement...|||Do you have any type of date criteria in your query?sql
Showing posts with label runs. Show all posts
Showing posts with label runs. Show all posts
Tuesday, March 27, 2012
Wednesday, March 21, 2012
DB Server: IDE vs. SCSI - What are the advantages?
Hi,
We are going to be setting up an e-commerce web site that runs on SQL Server 2000 standard and we need to purchase a database server and are not sure whether we will have a use for SCSI hard drives or can stick with IDE RAID.
The server will just run SQL SERVER 2000, nothing else. There are no images, or anything like that being stored in the database or on the server, but credit card transactions, and orders will be taking place so we want that to be efficient.
Can someone give a good explanation of which (IDE or RAID) would server us better and why?
Thank you,
MarkMark, you haven't given nearly enogh info to make an analysis of any kind but I can offer some clarification...
First you definatly want some kind of RAID technology. This can give you drive redundency and depending on he kind of card purchased, some flexability in how you partition your drives.
IDE vs SCSI. It is true you can save some money by going IDE but the technology hasn't been around long enough. If you absolutly had to cut costs to the bone and purchased IDE then I would watch the error logs like a hawk for any problems that might arrise.
SCSI technology has been around for ever and provided you go with a name brand, you shouldn't have any surprises. SCSI has been and continues to be the backbone for data storage. I have 15 year old SCSI drives that continue to spin and serve up data without ANY problems, wish I could say the same for my IDE drives.
Other Comments anyone?|||Paul, what information would be required for an analysis? I have no clue how MSSQL stores information or uses a hard drive when performing queries and inserts, etc. I just want to understand how MSSQL interacts with the hard drive... does it require more memory and not rely as heavily on the hard drive? Those types of questions.
Thanks,
Mark|||Since you are going to have high level of concurent writes to db, the logical choice is to inplement RAID10. Only few IDE hardware can support RAID10. SCSI disks are much faster and have high throughput.
I think SCSI if the only choise for production at this moment.
HTH,
OBRP
We are going to be setting up an e-commerce web site that runs on SQL Server 2000 standard and we need to purchase a database server and are not sure whether we will have a use for SCSI hard drives or can stick with IDE RAID.
The server will just run SQL SERVER 2000, nothing else. There are no images, or anything like that being stored in the database or on the server, but credit card transactions, and orders will be taking place so we want that to be efficient.
Can someone give a good explanation of which (IDE or RAID) would server us better and why?
Thank you,
MarkMark, you haven't given nearly enogh info to make an analysis of any kind but I can offer some clarification...
First you definatly want some kind of RAID technology. This can give you drive redundency and depending on he kind of card purchased, some flexability in how you partition your drives.
IDE vs SCSI. It is true you can save some money by going IDE but the technology hasn't been around long enough. If you absolutly had to cut costs to the bone and purchased IDE then I would watch the error logs like a hawk for any problems that might arrise.
SCSI technology has been around for ever and provided you go with a name brand, you shouldn't have any surprises. SCSI has been and continues to be the backbone for data storage. I have 15 year old SCSI drives that continue to spin and serve up data without ANY problems, wish I could say the same for my IDE drives.
Other Comments anyone?|||Paul, what information would be required for an analysis? I have no clue how MSSQL stores information or uses a hard drive when performing queries and inserts, etc. I just want to understand how MSSQL interacts with the hard drive... does it require more memory and not rely as heavily on the hard drive? Those types of questions.
Thanks,
Mark|||Since you are going to have high level of concurent writes to db, the logical choice is to inplement RAID10. Only few IDE hardware can support RAID10. SCSI disks are much faster and have high throughput.
I think SCSI if the only choise for production at this moment.
HTH,
OBRP
Wednesday, March 7, 2012
DB name changes daily
Hi,
I am trying to write SQL automation that runs SQL scripts against the daily drop of the database. Each day, a new DB is created using the format (Testdb + xxxxx )where xxxxx= computed build number. Thus today's db name might be TEST12345 and tomorrow wo
uld be Test12346. I have a script that creates the dbname such as Test12345 and when I run it, I get the expected result, but when I try this statement
Use @.dbname
I get an error.
Is there a way to introduce a variable with a Use statement so that I can automatically open the latest db?
Thanks
Hi,
'USE' statement will change the DB context for the current connection and
then go back to where it was before (Default datbase context).
Doing this in an exec will change the context until the exec is completed
and then go back to default database context.
This is small example to show how to do it. This example uses the Northwind
database.
This database has a stored procedure 'emp_list' . From the master database
and execute the following:-
set quoted_identifier off
declare @.dbname varchar(15), @.procname varchar(25)
declare @.startdate varchar(10) , @.enddate varchar(10)
set @.dbname = 'northwind'
set @.procname = '[emp_list]'
exec ('USE ' + @.dbname + ' execute ' + @.procname )
the last statement translates to:
exec ( USE northwind execute [emp_list])
But after the execution again the context will go to the default database.
Note:
The best option rather than using the above is:- (SP_DEFAULTDB)
1. Have a common login
2. Every time after creating the new database use the sp_defaultdb procedure
to change the default db of the login
3. After this when ever that user logins the database context will be the
new database and there is nolt required to give USE XXXXX
Thanks
Hari
MCDBA
"Tomas" <anonymous@.discussions.microsoft.com> wrote in message
news:F9E1B6D0-5378-45AD-8A2C-6DF194B86B0A@.microsoft.com...
> Hi,
> I am trying to write SQL automation that runs SQL scripts against the
daily drop of the database. Each day, a new DB is created using the format
(Testdb + xxxxx )where xxxxx= computed build number. Thus today's db name
might be TEST12345 and tomorrow would be Test12346. I have a script that
creates the dbname such as Test12345 and when I run it, I get the expected
result, but when I try this statement
> Use @.dbname
> I get an error.
> Is there a way to introduce a variable with a Use statement so that I can
automatically open the latest db?
> Thanks
I am trying to write SQL automation that runs SQL scripts against the daily drop of the database. Each day, a new DB is created using the format (Testdb + xxxxx )where xxxxx= computed build number. Thus today's db name might be TEST12345 and tomorrow wo
uld be Test12346. I have a script that creates the dbname such as Test12345 and when I run it, I get the expected result, but when I try this statement
Use @.dbname
I get an error.
Is there a way to introduce a variable with a Use statement so that I can automatically open the latest db?
Thanks
Hi,
'USE' statement will change the DB context for the current connection and
then go back to where it was before (Default datbase context).
Doing this in an exec will change the context until the exec is completed
and then go back to default database context.
This is small example to show how to do it. This example uses the Northwind
database.
This database has a stored procedure 'emp_list' . From the master database
and execute the following:-
set quoted_identifier off
declare @.dbname varchar(15), @.procname varchar(25)
declare @.startdate varchar(10) , @.enddate varchar(10)
set @.dbname = 'northwind'
set @.procname = '[emp_list]'
exec ('USE ' + @.dbname + ' execute ' + @.procname )
the last statement translates to:
exec ( USE northwind execute [emp_list])
But after the execution again the context will go to the default database.
Note:
The best option rather than using the above is:- (SP_DEFAULTDB)
1. Have a common login
2. Every time after creating the new database use the sp_defaultdb procedure
to change the default db of the login
3. After this when ever that user logins the database context will be the
new database and there is nolt required to give USE XXXXX
Thanks
Hari
MCDBA
"Tomas" <anonymous@.discussions.microsoft.com> wrote in message
news:F9E1B6D0-5378-45AD-8A2C-6DF194B86B0A@.microsoft.com...
> Hi,
> I am trying to write SQL automation that runs SQL scripts against the
daily drop of the database. Each day, a new DB is created using the format
(Testdb + xxxxx )where xxxxx= computed build number. Thus today's db name
might be TEST12345 and tomorrow would be Test12346. I have a script that
creates the dbname such as Test12345 and when I run it, I get the expected
result, but when I try this statement
> Use @.dbname
> I get an error.
> Is there a way to introduce a variable with a Use statement so that I can
automatically open the latest db?
> Thanks
Saturday, February 25, 2012
DB Maintenance Plan not backing up 2 databases..
I have a DB Maintenance plan that runs and indicates success and shows
nothing in the log, but doesn't ever write the backup file to disk. This is
happening for 2 out of 3 databases that are backed up in this plan. All
databases are set to Full recovery mode and I can back all of them up
manually without a problem. I also use BackupExec with the SQL agent and it
works fine. I've been searching the archives looking for anything along
these lines, but I haven't found anything yet. Here is some additional
environment info in case it makes any difference.
Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Standard Edition on
Windows NT 5.2 (Build 3790
Snip from Log Output:
[4] Database DYNAMICS: Database Backup...
Destination: [E:\SQL Backups\Great
Plains\Databases\DYNAMICS\DYNAMICS_db_200506151939 .BAK]
** Execution Time: 0 hrs, 0 mins, 1 secs **
[5] Database HBLLC: Database Backup...
Destination: [E:\SQL Backups\Great
Plains\Databases\HBLLC\HBLLC_db_200506151939.BAK]
** Execution Time: 0 hrs, 0 mins, 3 secs **
[6] Database HCB: Database Backup...
Destination: [E:\SQL Backups\Great
Plains\Databases\HCB\HCB_db_200506151939.BAK]
** Execution Time: 0 hrs, 0 mins, 7 secs **
I have it set to write backups to E:\SQL Backups\Great Plains\Databases and
to create subfolders. If I delete the HCB and HCBLLC folders, it does
re-create them. I currently have the SQL Agent running from a domain level
admin account (temporarily for testing) to rule out any possible rights
issues.
I'm stumped.. any Help would be appreciated..
Matt
Is there anything in the SQL errorlog or NT Event log (normally
Application log) that corresponds with the DB maint plan execution times
that may shed some light on the problem? Is E: a mapped network drive
or a local volume? Have you tried creating a new maint plan for one of
the problem DBs on its own?
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Matt Williamson wrote:
>I have a DB Maintenance plan that runs and indicates success and shows
>nothing in the log, but doesn't ever write the backup file to disk. This is
>happening for 2 out of 3 databases that are backed up in this plan. All
>databases are set to Full recovery mode and I can back all of them up
>manually without a problem. I also use BackupExec with the SQL agent and it
>works fine. I've been searching the archives looking for anything along
>these lines, but I haven't found anything yet. Here is some additional
>environment info in case it makes any difference.
>Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Standard Edition on
>Windows NT 5.2 (Build 3790
>Snip from Log Output:
>
>[4] Database DYNAMICS: Database Backup...
> Destination: [E:\SQL Backups\Great
>Plains\Databases\DYNAMICS\DYNAMICS_db_20050615193 9.BAK]
> ** Execution Time: 0 hrs, 0 mins, 1 secs **
>[5] Database HBLLC: Database Backup...
> Destination: [E:\SQL Backups\Great
>Plains\Databases\HBLLC\HBLLC_db_200506151939.BA K]
> ** Execution Time: 0 hrs, 0 mins, 3 secs **
>[6] Database HCB: Database Backup...
> Destination: [E:\SQL Backups\Great
>Plains\Databases\HCB\HCB_db_200506151939.BAK]
> ** Execution Time: 0 hrs, 0 mins, 7 secs **
>
>I have it set to write backups to E:\SQL Backups\Great Plains\Databases and
>to create subfolders. If I delete the HCB and HCBLLC folders, it does
>re-create them. I currently have the SQL Agent running from a domain level
>admin account (temporarily for testing) to rule out any possible rights
>issues.
>I'm stumped.. any Help would be appreciated..
>Matt
>
>
nothing in the log, but doesn't ever write the backup file to disk. This is
happening for 2 out of 3 databases that are backed up in this plan. All
databases are set to Full recovery mode and I can back all of them up
manually without a problem. I also use BackupExec with the SQL agent and it
works fine. I've been searching the archives looking for anything along
these lines, but I haven't found anything yet. Here is some additional
environment info in case it makes any difference.
Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Standard Edition on
Windows NT 5.2 (Build 3790
Snip from Log Output:
[4] Database DYNAMICS: Database Backup...
Destination: [E:\SQL Backups\Great
Plains\Databases\DYNAMICS\DYNAMICS_db_200506151939 .BAK]
** Execution Time: 0 hrs, 0 mins, 1 secs **
[5] Database HBLLC: Database Backup...
Destination: [E:\SQL Backups\Great
Plains\Databases\HBLLC\HBLLC_db_200506151939.BAK]
** Execution Time: 0 hrs, 0 mins, 3 secs **
[6] Database HCB: Database Backup...
Destination: [E:\SQL Backups\Great
Plains\Databases\HCB\HCB_db_200506151939.BAK]
** Execution Time: 0 hrs, 0 mins, 7 secs **
I have it set to write backups to E:\SQL Backups\Great Plains\Databases and
to create subfolders. If I delete the HCB and HCBLLC folders, it does
re-create them. I currently have the SQL Agent running from a domain level
admin account (temporarily for testing) to rule out any possible rights
issues.
I'm stumped.. any Help would be appreciated..
Matt
Is there anything in the SQL errorlog or NT Event log (normally
Application log) that corresponds with the DB maint plan execution times
that may shed some light on the problem? Is E: a mapped network drive
or a local volume? Have you tried creating a new maint plan for one of
the problem DBs on its own?
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Matt Williamson wrote:
>I have a DB Maintenance plan that runs and indicates success and shows
>nothing in the log, but doesn't ever write the backup file to disk. This is
>happening for 2 out of 3 databases that are backed up in this plan. All
>databases are set to Full recovery mode and I can back all of them up
>manually without a problem. I also use BackupExec with the SQL agent and it
>works fine. I've been searching the archives looking for anything along
>these lines, but I haven't found anything yet. Here is some additional
>environment info in case it makes any difference.
>Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Standard Edition on
>Windows NT 5.2 (Build 3790
>Snip from Log Output:
>
>[4] Database DYNAMICS: Database Backup...
> Destination: [E:\SQL Backups\Great
>Plains\Databases\DYNAMICS\DYNAMICS_db_20050615193 9.BAK]
> ** Execution Time: 0 hrs, 0 mins, 1 secs **
>[5] Database HBLLC: Database Backup...
> Destination: [E:\SQL Backups\Great
>Plains\Databases\HBLLC\HBLLC_db_200506151939.BA K]
> ** Execution Time: 0 hrs, 0 mins, 3 secs **
>[6] Database HCB: Database Backup...
> Destination: [E:\SQL Backups\Great
>Plains\Databases\HCB\HCB_db_200506151939.BAK]
> ** Execution Time: 0 hrs, 0 mins, 7 secs **
>
>I have it set to write backups to E:\SQL Backups\Great Plains\Databases and
>to create subfolders. If I delete the HCB and HCBLLC folders, it does
>re-create them. I currently have the SQL Agent running from a domain level
>admin account (temporarily for testing) to rule out any possible rights
>issues.
>I'm stumped.. any Help would be appreciated..
>Matt
>
>
DB Maintenance Plan not backing up 2 databases..
I have a DB Maintenance plan that runs and indicates success and shows
nothing in the log, but doesn't ever write the backup file to disk. This is
happening for 2 out of 3 databases that are backed up in this plan. All
databases are set to Full recovery mode and I can back all of them up
manually without a problem. I also use BackupExec with the SQL agent and it
works fine. I've been searching the archives looking for anything along
these lines, but I haven't found anything yet. Here is some additional
environment info in case it makes any difference.
Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Standard Edition on
Windows NT 5.2 (Build 3790
Snip from Log Output:
[4] Database DYNAMICS: Database Backup...
Destination: [E:\SQL Backups\Great
Plains\Databases\DYNAMICS\DYNAMICS_db_20
0506151939.BAK]
** Execution Time: 0 hrs, 0 mins, 1 secs **
[5] Database HBLLC: Database Backup...
Destination: [E:\SQL Backups\Great
Plains\Databases\HBLLC\HBLLC_db_20050615
1939.BAK]
** Execution Time: 0 hrs, 0 mins, 3 secs **
[6] Database HCB: Database Backup...
Destination: [E:\SQL Backups\Great
Plains\Databases\HCB\HCB_db_200506151939
.BAK]
** Execution Time: 0 hrs, 0 mins, 7 secs **
I have it set to write backups to E:\SQL Backups\Great Plains\Databases and
to create subfolders. If I delete the HCB and HCBLLC folders, it does
re-create them. I currently have the SQL Agent running from a domain level
admin account (temporarily for testing) to rule out any possible rights
issues.
I'm stumped.. any Help would be appreciated..
MattIs there anything in the SQL errorlog or NT Event log (normally
Application log) that corresponds with the DB maint plan execution times
that may shed some light on the problem? Is E: a mapped network drive
or a local volume? Have you tried creating a new maint plan for one of
the problem DBs on its own?
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Matt Williamson wrote:
>I have a DB Maintenance plan that runs and indicates success and shows
>nothing in the log, but doesn't ever write the backup file to disk. This is
>happening for 2 out of 3 databases that are backed up in this plan. All
>databases are set to Full recovery mode and I can back all of them up
>manually without a problem. I also use BackupExec with the SQL agent and it
>works fine. I've been searching the archives looking for anything along
>these lines, but I haven't found anything yet. Here is some additional
>environment info in case it makes any difference.
>Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Standard Edition on
>Windows NT 5.2 (Build 3790
>Snip from Log Output:
>
>[4] Database DYNAMICS: Database Backup...
> Destination: [E:\SQL Backups\Great
> Plains\Databases\DYNAMICS\DYNAMICS_db_20
0506151939.BAK]
> ** Execution Time: 0 hrs, 0 mins, 1 secs **
>[5] Database HBLLC: Database Backup...
> Destination: [E:\SQL Backups\Great
> Plains\Databases\HBLLC\HBLLC_db_20050615
1939.BAK]
> ** Execution Time: 0 hrs, 0 mins, 3 secs **
>[6] Database HCB: Database Backup...
> Destination: [E:\SQL Backups\Great
> Plains\Databases\HCB\HCB_db_200506151939
.BAK]
> ** Execution Time: 0 hrs, 0 mins, 7 secs **
>
>I have it set to write backups to E:\SQL Backups\Great Plains\Databases and
>to create subfolders. If I delete the HCB and HCBLLC folders, it does
>re-create them. I currently have the SQL Agent running from a domain level
>admin account (temporarily for testing) to rule out any possible rights
>issues.
>I'm stumped.. any Help would be appreciated..
>Matt
>
>
nothing in the log, but doesn't ever write the backup file to disk. This is
happening for 2 out of 3 databases that are backed up in this plan. All
databases are set to Full recovery mode and I can back all of them up
manually without a problem. I also use BackupExec with the SQL agent and it
works fine. I've been searching the archives looking for anything along
these lines, but I haven't found anything yet. Here is some additional
environment info in case it makes any difference.
Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Standard Edition on
Windows NT 5.2 (Build 3790
Snip from Log Output:
[4] Database DYNAMICS: Database Backup...
Destination: [E:\SQL Backups\Great
Plains\Databases\DYNAMICS\DYNAMICS_db_20
0506151939.BAK]
** Execution Time: 0 hrs, 0 mins, 1 secs **
[5] Database HBLLC: Database Backup...
Destination: [E:\SQL Backups\Great
Plains\Databases\HBLLC\HBLLC_db_20050615
1939.BAK]
** Execution Time: 0 hrs, 0 mins, 3 secs **
[6] Database HCB: Database Backup...
Destination: [E:\SQL Backups\Great
Plains\Databases\HCB\HCB_db_200506151939
.BAK]
** Execution Time: 0 hrs, 0 mins, 7 secs **
I have it set to write backups to E:\SQL Backups\Great Plains\Databases and
to create subfolders. If I delete the HCB and HCBLLC folders, it does
re-create them. I currently have the SQL Agent running from a domain level
admin account (temporarily for testing) to rule out any possible rights
issues.
I'm stumped.. any Help would be appreciated..
MattIs there anything in the SQL errorlog or NT Event log (normally
Application log) that corresponds with the DB maint plan execution times
that may shed some light on the problem? Is E: a mapped network drive
or a local volume? Have you tried creating a new maint plan for one of
the problem DBs on its own?
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Matt Williamson wrote:
>I have a DB Maintenance plan that runs and indicates success and shows
>nothing in the log, but doesn't ever write the backup file to disk. This is
>happening for 2 out of 3 databases that are backed up in this plan. All
>databases are set to Full recovery mode and I can back all of them up
>manually without a problem. I also use BackupExec with the SQL agent and it
>works fine. I've been searching the archives looking for anything along
>these lines, but I haven't found anything yet. Here is some additional
>environment info in case it makes any difference.
>Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Standard Edition on
>Windows NT 5.2 (Build 3790
>Snip from Log Output:
>
>[4] Database DYNAMICS: Database Backup...
> Destination: [E:\SQL Backups\Great
> Plains\Databases\DYNAMICS\DYNAMICS_db_20
0506151939.BAK]
> ** Execution Time: 0 hrs, 0 mins, 1 secs **
>[5] Database HBLLC: Database Backup...
> Destination: [E:\SQL Backups\Great
> Plains\Databases\HBLLC\HBLLC_db_20050615
1939.BAK]
> ** Execution Time: 0 hrs, 0 mins, 3 secs **
>[6] Database HCB: Database Backup...
> Destination: [E:\SQL Backups\Great
> Plains\Databases\HCB\HCB_db_200506151939
.BAK]
> ** Execution Time: 0 hrs, 0 mins, 7 secs **
>
>I have it set to write backups to E:\SQL Backups\Great Plains\Databases and
>to create subfolders. If I delete the HCB and HCBLLC folders, it does
>re-create them. I currently have the SQL Agent running from a domain level
>admin account (temporarily for testing) to rule out any possible rights
>issues.
>I'm stumped.. any Help would be appreciated..
>Matt
>
>
DB Maintenance Plan not backing up 2 databases..
I have a DB Maintenance plan that runs and indicates success and shows
nothing in the log, but doesn't ever write the backup file to disk. This is
happening for 2 out of 3 databases that are backed up in this plan. All
databases are set to Full recovery mode and I can back all of them up
manually without a problem. I also use BackupExec with the SQL agent and it
works fine. I've been searching the archives looking for anything along
these lines, but I haven't found anything yet. Here is some additional
environment info in case it makes any difference.
Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Standard Edition on
Windows NT 5.2 (Build 3790:)
Snip from Log Output:
[4] Database DYNAMICS: Database Backup...
Destination: [E:\SQL Backups\Great
Plains\Databases\DYNAMICS\DYNAMICS_db_200506151939.BAK]
** Execution Time: 0 hrs, 0 mins, 1 secs **
[5] Database HBLLC: Database Backup...
Destination: [E:\SQL Backups\Great
Plains\Databases\HBLLC\HBLLC_db_200506151939.BAK]
** Execution Time: 0 hrs, 0 mins, 3 secs **
[6] Database HCB: Database Backup...
Destination: [E:\SQL Backups\Great
Plains\Databases\HCB\HCB_db_200506151939.BAK]
** Execution Time: 0 hrs, 0 mins, 7 secs **
I have it set to write backups to E:\SQL Backups\Great Plains\Databases and
to create subfolders. If I delete the HCB and HCBLLC folders, it does
re-create them. I currently have the SQL Agent running from a domain level
admin account (temporarily for testing) to rule out any possible rights
issues.
I'm stumped.. any Help would be appreciated..
MattThis is a multi-part message in MIME format.
--080206070603060002020104
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Is there anything in the SQL errorlog or NT Event log (normally
Application log) that corresponds with the DB maint plan execution times
that may shed some light on the problem? Is E: a mapped network drive
or a local volume? Have you tried creating a new maint plan for one of
the problem DBs on its own?
--
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Matt Williamson wrote:
>I have a DB Maintenance plan that runs and indicates success and shows
>nothing in the log, but doesn't ever write the backup file to disk. This is
>happening for 2 out of 3 databases that are backed up in this plan. All
>databases are set to Full recovery mode and I can back all of them up
>manually without a problem. I also use BackupExec with the SQL agent and it
>works fine. I've been searching the archives looking for anything along
>these lines, but I haven't found anything yet. Here is some additional
>environment info in case it makes any difference.
>Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Standard Edition on
>Windows NT 5.2 (Build 3790:)
>Snip from Log Output:
>
>[4] Database DYNAMICS: Database Backup...
> Destination: [E:\SQL Backups\Great
>Plains\Databases\DYNAMICS\DYNAMICS_db_200506151939.BAK]
> ** Execution Time: 0 hrs, 0 mins, 1 secs **
>[5] Database HBLLC: Database Backup...
> Destination: [E:\SQL Backups\Great
>Plains\Databases\HBLLC\HBLLC_db_200506151939.BAK]
> ** Execution Time: 0 hrs, 0 mins, 3 secs **
>[6] Database HCB: Database Backup...
> Destination: [E:\SQL Backups\Great
>Plains\Databases\HCB\HCB_db_200506151939.BAK]
> ** Execution Time: 0 hrs, 0 mins, 7 secs **
>
>I have it set to write backups to E:\SQL Backups\Great Plains\Databases and
>to create subfolders. If I delete the HCB and HCBLLC folders, it does
>re-create them. I currently have the SQL Agent running from a domain level
>admin account (temporarily for testing) to rule out any possible rights
>issues.
>I'm stumped.. any Help would be appreciated..
>Matt
>
>
--080206070603060002020104
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>Is there anything in the SQL errorlog or NT Event log (normally
Application log) that corresponds with the DB maint plan execution
times that may shed some light on the problem? Is E: a mapped network
drive or a local volume? Have you tried creating a new maint plan for
one of the problem DBs on its own?</tt><br>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font> </span><b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"> <font face="Tahoma"
size="2">|</font><i><font face="Tahoma"> </font><font face="Tahoma"
size="2"> database administrator</font></i><font face="Tahoma" size="2">
| mallesons</font><font face="Tahoma"> </font><font face="Tahoma"
size="2">stephen</font><font face="Tahoma"> </font><font face="Tahoma"
size="2"> jaques</font><font face="Tahoma"><br>
</font><b><font face="Tahoma" size="2">T</font></b><font face="Tahoma"
size="2"> +61 (2) 9296 3668 |</font><b><font face="Tahoma"> </font><font
face="Tahoma" size="2"> F</font></b><font face="Tahoma" size="2"> +61
(2) 9296 3885 |</font><b><font face="Tahoma"> </font><font
face="Tahoma" size="2">M</font></b><font face="Tahoma" size="2"> +61
(408) 675 907</font><br>
<b><font face="Tahoma" size="2">E</font></b><font face="Tahoma" size="2">
<a href="http://links.10026.com/?link=mailto:mike.hodgson@.mallesons.nospam.com">
mailto:mike.hodgson@.mallesons.nospam.com</a> |</font><b><font
face="Tahoma"> </font><font face="Tahoma" size="2">W</font></b><font
face="Tahoma" size="2"> <a href="http://links.10026.com/?link=/">http://www.mallesons.com">
http://www.mallesons.com</a></font></span> </p>
</div>
<br>
<br>
Matt Williamson wrote:
<blockquote cite="miduIb6VfgcFHA.616@.TK2MSFTNGP12.phx.gbl" type="cite">
<pre wrap="">I have a DB Maintenance plan that runs and indicates success and shows
nothing in the log, but doesn't ever write the backup file to disk. This is
happening for 2 out of 3 databases that are backed up in this plan. All
databases are set to Full recovery mode and I can back all of them up
manually without a problem. I also use BackupExec with the SQL agent and it
works fine. I've been searching the archives looking for anything along
these lines, but I haven't found anything yet. Here is some additional
environment info in case it makes any difference.
Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Standard Edition on
Windows NT 5.2 (Build 3790:)
Snip from Log Output:
[4] Database DYNAMICS: Database Backup...
Destination: [E:\SQL Backups\Great
Plains\Databases\DYNAMICS\DYNAMICS_db_200506151939.BAK]
** Execution Time: 0 hrs, 0 mins, 1 secs **
[5] Database HBLLC: Database Backup...
Destination: [E:\SQL Backups\Great
Plains\Databases\HBLLC\HBLLC_db_200506151939.BAK]
** Execution Time: 0 hrs, 0 mins, 3 secs **
[6] Database HCB: Database Backup...
Destination: [E:\SQL Backups\Great
Plains\Databases\HCB\HCB_db_200506151939.BAK]
** Execution Time: 0 hrs, 0 mins, 7 secs **
I have it set to write backups to E:\SQL Backups\Great Plains\Databases and
to create subfolders. If I delete the HCB and HCBLLC folders, it does
re-create them. I currently have the SQL Agent running from a domain level
admin account (temporarily for testing) to rule out any possible rights
issues.
I'm stumped.. any Help would be appreciated..
Matt
</pre>
</blockquote>
</body>
</html>
--080206070603060002020104--
nothing in the log, but doesn't ever write the backup file to disk. This is
happening for 2 out of 3 databases that are backed up in this plan. All
databases are set to Full recovery mode and I can back all of them up
manually without a problem. I also use BackupExec with the SQL agent and it
works fine. I've been searching the archives looking for anything along
these lines, but I haven't found anything yet. Here is some additional
environment info in case it makes any difference.
Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Standard Edition on
Windows NT 5.2 (Build 3790:)
Snip from Log Output:
[4] Database DYNAMICS: Database Backup...
Destination: [E:\SQL Backups\Great
Plains\Databases\DYNAMICS\DYNAMICS_db_200506151939.BAK]
** Execution Time: 0 hrs, 0 mins, 1 secs **
[5] Database HBLLC: Database Backup...
Destination: [E:\SQL Backups\Great
Plains\Databases\HBLLC\HBLLC_db_200506151939.BAK]
** Execution Time: 0 hrs, 0 mins, 3 secs **
[6] Database HCB: Database Backup...
Destination: [E:\SQL Backups\Great
Plains\Databases\HCB\HCB_db_200506151939.BAK]
** Execution Time: 0 hrs, 0 mins, 7 secs **
I have it set to write backups to E:\SQL Backups\Great Plains\Databases and
to create subfolders. If I delete the HCB and HCBLLC folders, it does
re-create them. I currently have the SQL Agent running from a domain level
admin account (temporarily for testing) to rule out any possible rights
issues.
I'm stumped.. any Help would be appreciated..
MattThis is a multi-part message in MIME format.
--080206070603060002020104
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Is there anything in the SQL errorlog or NT Event log (normally
Application log) that corresponds with the DB maint plan execution times
that may shed some light on the problem? Is E: a mapped network drive
or a local volume? Have you tried creating a new maint plan for one of
the problem DBs on its own?
--
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Matt Williamson wrote:
>I have a DB Maintenance plan that runs and indicates success and shows
>nothing in the log, but doesn't ever write the backup file to disk. This is
>happening for 2 out of 3 databases that are backed up in this plan. All
>databases are set to Full recovery mode and I can back all of them up
>manually without a problem. I also use BackupExec with the SQL agent and it
>works fine. I've been searching the archives looking for anything along
>these lines, but I haven't found anything yet. Here is some additional
>environment info in case it makes any difference.
>Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Standard Edition on
>Windows NT 5.2 (Build 3790:)
>Snip from Log Output:
>
>[4] Database DYNAMICS: Database Backup...
> Destination: [E:\SQL Backups\Great
>Plains\Databases\DYNAMICS\DYNAMICS_db_200506151939.BAK]
> ** Execution Time: 0 hrs, 0 mins, 1 secs **
>[5] Database HBLLC: Database Backup...
> Destination: [E:\SQL Backups\Great
>Plains\Databases\HBLLC\HBLLC_db_200506151939.BAK]
> ** Execution Time: 0 hrs, 0 mins, 3 secs **
>[6] Database HCB: Database Backup...
> Destination: [E:\SQL Backups\Great
>Plains\Databases\HCB\HCB_db_200506151939.BAK]
> ** Execution Time: 0 hrs, 0 mins, 7 secs **
>
>I have it set to write backups to E:\SQL Backups\Great Plains\Databases and
>to create subfolders. If I delete the HCB and HCBLLC folders, it does
>re-create them. I currently have the SQL Agent running from a domain level
>admin account (temporarily for testing) to rule out any possible rights
>issues.
>I'm stumped.. any Help would be appreciated..
>Matt
>
>
--080206070603060002020104
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>Is there anything in the SQL errorlog or NT Event log (normally
Application log) that corresponds with the DB maint plan execution
times that may shed some light on the problem? Is E: a mapped network
drive or a local volume? Have you tried creating a new maint plan for
one of the problem DBs on its own?</tt><br>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font> </span><b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"> <font face="Tahoma"
size="2">|</font><i><font face="Tahoma"> </font><font face="Tahoma"
size="2"> database administrator</font></i><font face="Tahoma" size="2">
| mallesons</font><font face="Tahoma"> </font><font face="Tahoma"
size="2">stephen</font><font face="Tahoma"> </font><font face="Tahoma"
size="2"> jaques</font><font face="Tahoma"><br>
</font><b><font face="Tahoma" size="2">T</font></b><font face="Tahoma"
size="2"> +61 (2) 9296 3668 |</font><b><font face="Tahoma"> </font><font
face="Tahoma" size="2"> F</font></b><font face="Tahoma" size="2"> +61
(2) 9296 3885 |</font><b><font face="Tahoma"> </font><font
face="Tahoma" size="2">M</font></b><font face="Tahoma" size="2"> +61
(408) 675 907</font><br>
<b><font face="Tahoma" size="2">E</font></b><font face="Tahoma" size="2">
<a href="http://links.10026.com/?link=mailto:mike.hodgson@.mallesons.nospam.com">
mailto:mike.hodgson@.mallesons.nospam.com</a> |</font><b><font
face="Tahoma"> </font><font face="Tahoma" size="2">W</font></b><font
face="Tahoma" size="2"> <a href="http://links.10026.com/?link=/">http://www.mallesons.com">
http://www.mallesons.com</a></font></span> </p>
</div>
<br>
<br>
Matt Williamson wrote:
<blockquote cite="miduIb6VfgcFHA.616@.TK2MSFTNGP12.phx.gbl" type="cite">
<pre wrap="">I have a DB Maintenance plan that runs and indicates success and shows
nothing in the log, but doesn't ever write the backup file to disk. This is
happening for 2 out of 3 databases that are backed up in this plan. All
databases are set to Full recovery mode and I can back all of them up
manually without a problem. I also use BackupExec with the SQL agent and it
works fine. I've been searching the archives looking for anything along
these lines, but I haven't found anything yet. Here is some additional
environment info in case it makes any difference.
Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Standard Edition on
Windows NT 5.2 (Build 3790:)
Snip from Log Output:
[4] Database DYNAMICS: Database Backup...
Destination: [E:\SQL Backups\Great
Plains\Databases\DYNAMICS\DYNAMICS_db_200506151939.BAK]
** Execution Time: 0 hrs, 0 mins, 1 secs **
[5] Database HBLLC: Database Backup...
Destination: [E:\SQL Backups\Great
Plains\Databases\HBLLC\HBLLC_db_200506151939.BAK]
** Execution Time: 0 hrs, 0 mins, 3 secs **
[6] Database HCB: Database Backup...
Destination: [E:\SQL Backups\Great
Plains\Databases\HCB\HCB_db_200506151939.BAK]
** Execution Time: 0 hrs, 0 mins, 7 secs **
I have it set to write backups to E:\SQL Backups\Great Plains\Databases and
to create subfolders. If I delete the HCB and HCBLLC folders, it does
re-create them. I currently have the SQL Agent running from a domain level
admin account (temporarily for testing) to rule out any possible rights
issues.
I'm stumped.. any Help would be appreciated..
Matt
</pre>
</blockquote>
</body>
</html>
--080206070603060002020104--
Sunday, February 19, 2012
DB Maint plan not shrinking files - why? Please help
Does anyone know why, when the database maintenance plan runs, that the physical data files are not shrunk to 10% as indicated in the plan? I have a 10 G data file (as part of about 10 data files in this db), 5 G used and 5 G unused, that doesn't seem to
release any of the allocated but unused portion of the file back to the OS. (SQL Server 2000)
because GUI doesnt really work. Try to write a script about it. The same way
a big transaction log wont shrink at all using GUI...
"Stephanie" <Stephanie@.discussions.microsoft.com> wrote in message
news:2824D45B-FFDE-42D9-BBB7-9EC05F070A3B@.microsoft.com...
> Does anyone know why, when the database maintenance plan runs, that the
physical data files are not shrunk to 10% as indicated in the plan? I have
a 10 G data file (as part of about 10 data files in this db), 5 G used and 5
G unused, that doesn't seem to release any of the allocated but unused
portion of the file back to the OS. (SQL Server 2000)
|||in BOL look at DBCC SHRINKDATABASE
You could use a job instead of MP !
"Stephanie" wrote:
> Does anyone know why, when the database maintenance plan runs, that the physical data files are not shrunk to 10% as indicated in the plan? I have a 10 G data file (as part of about 10 data files in this db), 5 G used and 5 G unused, that doesn't seem
to release any of the allocated but unused portion of the file back to the OS. (SQL Server 2000)
release any of the allocated but unused portion of the file back to the OS. (SQL Server 2000)
because GUI doesnt really work. Try to write a script about it. The same way
a big transaction log wont shrink at all using GUI...
"Stephanie" <Stephanie@.discussions.microsoft.com> wrote in message
news:2824D45B-FFDE-42D9-BBB7-9EC05F070A3B@.microsoft.com...
> Does anyone know why, when the database maintenance plan runs, that the
physical data files are not shrunk to 10% as indicated in the plan? I have
a 10 G data file (as part of about 10 data files in this db), 5 G used and 5
G unused, that doesn't seem to release any of the allocated but unused
portion of the file back to the OS. (SQL Server 2000)
|||in BOL look at DBCC SHRINKDATABASE
You could use a job instead of MP !
"Stephanie" wrote:
> Does anyone know why, when the database maintenance plan runs, that the physical data files are not shrunk to 10% as indicated in the plan? I have a 10 G data file (as part of about 10 data files in this db), 5 G used and 5 G unused, that doesn't seem
to release any of the allocated but unused portion of the file back to the OS. (SQL Server 2000)
Subscribe to:
Posts (Atom)