DB Will not Grow

I am running SQL Server 2000 with SP3 and all avail hotfixes and security patches since then. I try to keep very current on patches and security hotfixes. It is on a rather large IBM Win 2K server box with all the latest SP and security hotfixes.

I have read the problem when the file can not grow fast enough to keep up and I agree with you that does not seem to be the problem. If that was the case I should still be able to grow the file manually with Enterprise manager or Alter Database command. When I try to change the file size in Enterprise manager it acts like it changes it but if I change it press OK then open it back up it is set back to what it was previously, and puts the following error in the SQL error logs:

Source spid65
fcb::ZeroFile(): GetOverLappedResult() failed with error 2.

The same error happens in the SQL error logs when running the Alter Database command even though it says it completed successfully it never changes the size.

I do think this error is the source of my problem but I can find it no where on the web, on Microsofts site, in this or any other message boards even. Either I have something set up very wrong or I found a new bug.

The real thing that gets me is that the transaction log grows just fine but the data file will not. Although if I try to shrink the transaction log file it will not shrink, which maybe related to this or it could be its own problem.

I have tried to set it to grow in MB rather than % I agree this should be the way it is always done, but that does not seem to help either.Try to capture the events using PROFILER during this process.

And refer to this KBA (http://support.microsoft.com/default.aspx?scid=KB;en-us;Q305635) for more information.|||Is this a clustered system? We are seeing the same problem after failing over our cluster, running SQL 2000 Enterprise w SP2. Prior to the failover last week, when our server guys installed some new OS patches, everything was normal. I added a second data file on the Primary filegroup as a short-term measure, but I'm going to call MS soon if I can't figure it out.

Thanks,
James

Db Trigger Question

Hello,

I have a db trigger (example below) that is supposed to update a column with the current date. However, I need to know which row is being updated by a user so that I would update ONLY this row. Is there a straightforward way to figure out which row is updated by a user? My table includes a column called rowid which is a primary key.

Thanks for any help!

CREATE TRIGGER dbo.[DatetimeUpdate]

ON dbo.DepartmentMapping] AFTER UPDATE

AS

BEGIN

SET NOCOUNT ON;

UPDATE dbo.DepartmentMapping

SET DateUpdated = GetDate()

Donnie:

You need to take advangage of the INSERTED pseudo-table in your trigger. Look up CREATE TRIGGER in books online and examine their examples. Your update ought to change to something like:

UPDATE dbo.DepartmentMapping
SET DateUpdated = GetDate()
FROM inserted i
INNER JOIN dbo.DepartmentMapping a
ON a.{departmentMappingKey} = i.{departmentMappingKey}

|||

When code inside a trigger is being executed, the code has access to two 'Virtual Tables' named Inserted and Deleted.

In the case of an UPDATE statement, the Deleted table contains the rows as they would have been before any changes and the Inserted table contains the rows as they are after the changes. The virtual tables have the same columns as the table on which the trigger is defined.

You could amend your code to that shown below.

Chris

UPDATE dm

SET DateUpdated = GetDate()

FROM dbo.DepartmentMapping dm

INNER JOIN deleted d ON d.DepartmentMappingID = dm.DepartmentMappingID

--or equally

--INNER JOIN inserted i ON i.DepartmentMappingID = dm.DepartmentMappingID

|||Great. Thank You!|||Of course you wouldn't want to use deleted in this case, since you would be trying to update a deleted row :)sql

DB Structural changes and reports

Our software is going through some changes, in the next 6 months the database
structure will have changed. I have written reports against the current
structure, is there any "easier" way to update reports other than going in
and giving an alias for the "new field name's"? I attempted this with the
most simplistic report we have and even though the change to the query was
just an alias it required me to change each field manually.
Is there an easier way? Maybe edit the rdl direct?
Thanks for any help.You could create a view which has the old names. Then edit the RDL to use
that instead of the current table name.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"DigitalVixen" <DigitalVixen@.discussions.microsoft.com> wrote in message
news:358965CE-B1EA-44E0-92BB-753631D9105B@.microsoft.com...
> Our software is going through some changes, in the next 6 months the
> database
> structure will have changed. I have written reports against the current
> structure, is there any "easier" way to update reports other than going in
> and giving an alias for the "new field name's"? I attempted this with the
> most simplistic report we have and even though the change to the query was
> just an alias it required me to change each field manually.
> Is there an easier way? Maybe edit the rdl direct?
> Thanks for any help.|||Thanks for the quick reply ;) Will this take care of the stored procedures
as well as tables?
"Bruce L-C [MVP]" wrote:
> You could create a view which has the old names. Then edit the RDL to use
> that instead of the current table name.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "DigitalVixen" <DigitalVixen@.discussions.microsoft.com> wrote in message
> news:358965CE-B1EA-44E0-92BB-753631D9105B@.microsoft.com...
> > Our software is going through some changes, in the next 6 months the
> > database
> > structure will have changed. I have written reports against the current
> > structure, is there any "easier" way to update reports other than going in
> > and giving an alias for the "new field name's"? I attempted this with the
> > most simplistic report we have and even though the change to the query was
> > just an alias it required me to change each field manually.
> >
> > Is there an easier way? Maybe edit the rdl direct?
> >
> > Thanks for any help.
>
>|||If you are using stored procedures then this is easier. Either you modify
the stored procedure to alias the fields in which case you don't need to
have a view or you create a view that does the aliasing and then modify the
stored procedure. Either way, if the change is in the stored procedure you
don't have to make any change to the report.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"DigitalVixen" <DigitalVixen@.discussions.microsoft.com> wrote in message
news:31CA7232-F5DA-494B-A908-44ACA4C2BC76@.microsoft.com...
> Thanks for the quick reply ;) Will this take care of the stored
> procedures
> as well as tables?
> "Bruce L-C [MVP]" wrote:
>> You could create a view which has the old names. Then edit the RDL to use
>> that instead of the current table name.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "DigitalVixen" <DigitalVixen@.discussions.microsoft.com> wrote in message
>> news:358965CE-B1EA-44E0-92BB-753631D9105B@.microsoft.com...
>> > Our software is going through some changes, in the next 6 months the
>> > database
>> > structure will have changed. I have written reports against the
>> > current
>> > structure, is there any "easier" way to update reports other than going
>> > in
>> > and giving an alias for the "new field name's"? I attempted this with
>> > the
>> > most simplistic report we have and even though the change to the query
>> > was
>> > just an alias it required me to change each field manually.
>> >
>> > Is there an easier way? Maybe edit the rdl direct?
>> >
>> > Thanks for any help.
>>

db size

I mean how do I expect the size of current DB after one year?
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:eiAzr$XtDHA.2392@.TK2MSFTNGP10.phx.gbl...
> What do you mean by "future sizing method"?
>
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
>
> "supernova" <abc@.yahoo.com> wrote in message
news:%23wL2kUVtDHA.2148@.TK2MSFTNGP12.phx.gbl...
> > Do you know where I can find information on SQL server future sizing
method?
> >
> >
>
>sn,
There is a nice section on sizing and capacity planning in the SQL Server
2000 Performance Tuning Technical Reference from Microsoft Press.
That's a good place to start.
Ron
--
Ron Talmage
SQL Server MVP
"supernova" <abc@.yahoo.com> wrote in message
news:uwi%23Wh7tDHA.560@.TK2MSFTNGP11.phx.gbl...
> I mean how do I expect the size of current DB after one year?
>
> "Tibor Karaszi"
<tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> wrote in message news:eiAzr$XtDHA.2392@.TK2MSFTNGP10.phx.gbl...
> > What do you mean by "future sizing method"?
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > Archive at:
>
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> > "supernova" <abc@.yahoo.com> wrote in message
> news:%23wL2kUVtDHA.2148@.TK2MSFTNGP12.phx.gbl...
> > > Do you know where I can find information on SQL server future sizing
> method?
> > >
> > >
> >
> >
>|||Is there anything on the web?
"Ron Talmage" <rtalmage@.prospice.com> wrote in message
news:OXq2QH8tDHA.2132@.TK2MSFTNGP10.phx.gbl...
> sn,
> There is a nice section on sizing and capacity planning in the SQL Server
> 2000 Performance Tuning Technical Reference from Microsoft Press.
> That's a good place to start.
> Ron
> --
> Ron Talmage
> SQL Server MVP
> "supernova" <abc@.yahoo.com> wrote in message
> news:uwi%23Wh7tDHA.560@.TK2MSFTNGP11.phx.gbl...
> > I mean how do I expect the size of current DB after one year?
> >
> >
> > "Tibor Karaszi"
> <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> > wrote in message news:eiAzr$XtDHA.2392@.TK2MSFTNGP10.phx.gbl...
> > > What do you mean by "future sizing method"?
> > >
> > > --
> > > Tibor Karaszi, SQL Server MVP
> > > Archive at:
> >
>
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
> > >
> > >
> > > "supernova" <abc@.yahoo.com> wrote in message
> > news:%23wL2kUVtDHA.2148@.TK2MSFTNGP12.phx.gbl...
> > > > Do you know where I can find information on SQL server future sizing
> > method?
> > > >
> > > >
> > >
> > >
> >
> >
>|||If you wish to project the size of the database you need to capture the
current size on a regular basis, and project from that...
"supernova" <abc@.yahoo.com> wrote in message
news:uwi#Wh7tDHA.560@.TK2MSFTNGP11.phx.gbl...
> I mean how do I expect the size of current DB after one year?
>
> "Tibor Karaszi"
<tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> wrote in message news:eiAzr$XtDHA.2392@.TK2MSFTNGP10.phx.gbl...
> > What do you mean by "future sizing method"?
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > Archive at:
>
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> > "supernova" <abc@.yahoo.com> wrote in message
> news:%23wL2kUVtDHA.2148@.TK2MSFTNGP12.phx.gbl...
> > > Do you know where I can find information on SQL server future sizing
> method?
> > >
> > >
> >
> >
>

DB Maintenance Plan

I have a SQL 2000 server that has a small but very important database (about 5GB). The current maintenance plan does trans logs every hour and full every day. Currently they are to file on the same drive array. I would like to send them to a share on another server just to be really safe.

Would it be better to

1. Redirect the maintenance plan so that trans logs and backups go directly to the share

or

2. Keep the maintenance plan back ups to the current location and write a script that runs every hour and copies the .bak files to the share.

Also, since the database is so small should I just do full backups every hour instead of transaction?

Hi,

assuming your DB recovery model is full , perform T-Log backup on hourly interval and daily differential backup and weekly full backup....

but ultimately its upto your requirement performing T-Log backup regularly will helps you recover point in time.

Regards

Hemantgiri S. Goswami

|||

Thanks for the tip regarding backup types.

What about getting the backups off of the SQL server? Should I redirect them during the backup process or should I let them go to a local drive and them copy them elsewhere?

|||

Hi,

Copy them on Network after taking backup on local drive. The reason is suppose their is a network congession or slow network connection while you taking backup dirctly on Network Drive you may get poor response and might be fails to write backup.

Refer below links for more

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=936771&SiteID=1

http://searchsqlserver.techtarget.com/featuredTopic/0,290042,sid87_gci1144141,00.html

http://www.lazydba.com/sql/1__849.html

http://www.windowsitpro.com/Article/ArticleID/14025/14025.html

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=6096

HTH

Hemantgiri S. Goswami

DB Maintenance Plan

I have a SQL 2000 server that has a small but very important database (about 5GB). The current maintenance plan does trans logs every hour and full every day. Currently they are to file on the same drive array. I would like to send them to a share on another server just to be really safe.

Would it be better to

1. Redirect the maintenance plan so that trans logs and backups go directly to the share

or

2. Keep the maintenance plan back ups to the current location and write a script that runs every hour and copies the .bak files to the share.

Also, since the database is so small should I just do full backups every hour instead of transaction?

Hi,

assuming your DB recovery model is full , perform T-Log backup on hourly interval and daily differential backup and weekly full backup....

but ultimately its upto your requirement performing T-Log backup regularly will helps you recover point in time.

Regards

Hemantgiri S. Goswami

|||

Thanks for the tip regarding backup types.

What about getting the backups off of the SQL server? Should I redirect them during the backup process or should I let them go to a local drive and them copy them elsewhere?

|||

Hi,

Copy them on Network after taking backup on local drive. The reason is suppose their is a network congession or slow network connection while you taking backup dirctly on Network Drive you may get poor response and might be fails to write backup.

Refer below links for more

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=936771&SiteID=1

http://searchsqlserver.techtarget.com/featuredTopic/0,290042,sid87_gci1144141,00.html

http://www.lazydba.com/sql/1__849.html

http://www.windowsitpro.com/Article/ArticleID/14025/14025.html

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=6096

HTH

Hemantgiri S. Goswami