Change Logical File Name Containing a Hyphen

I'm running SQL Server 2000. I have a DB with a logical file name containing
a hyphen. When trying to change the size of the db and log files by running
the following SQL statement it throws an error stating the file name is not
listed in sysfiles:
-- Modify db file size and max size
alter database DB_NAME_DS
MODIFY FILE
(
NAME = [DB_NAME-DS_DATA],
SIZE = 50MB,
MAXSIZE = 100MB,
FILEGROWTH = 50MB
)
-- Modify log file size and max size
alter database DB_NAME_DS
MODIFY FILE
(
NAME = [DB_NAME-DS_Log],
SIZE = 50MB,
MAXSIZE = 100MB,
FILEGROWTH = 50MB
)
GO
I've verified the logical file names using SP_HELPDB. When the DB was
created I did not specify any logical file names. SQL Server assigned them
automatically and put the hyphens in. What is causing the problem with the
SQL command and how can I get around this problem.
Thanks for any help!Please post result of following:
sp_helpfile
"Skippy, DOT DBA" wrote:
> I'm running SQL Server 2000. I have a DB with a logical file name containing
> a hyphen. When trying to change the size of the db and log files by running
> the following SQL statement it throws an error stating the file name is not
> listed in sysfiles:
> -- Modify db file size and max size
> alter database DB_NAME_DS
> MODIFY FILE
> (
> NAME = [DB_NAME-DS_DATA],
> SIZE = 50MB,
> MAXSIZE = 100MB,
> FILEGROWTH = 50MB
> )
> -- Modify log file size and max size
> alter database DB_NAME_DS
> MODIFY FILE
> (
> NAME = [DB_NAME-DS_Log],
> SIZE = 50MB,
> MAXSIZE = 100MB,
> FILEGROWTH = 50MB
> )
> GO
> I've verified the logical file names using SP_HELPDB. When the DB was
> created I did not specify any logical file names. SQL Server assigned them
> automatically and put the hyphens in. What is causing the problem with the
> SQL command and how can I get around this problem.
> Thanks for any help!|||SP_HELPFILE results:
DB_NAME-DS_Data
1 <drive
letter>:\data\DB_NAME-DS_Data.MDF
PRIMARY 32896 KB Unlimited 10% data only
DB_NAME-DS_Log
2 <drive
letter>:\Logs\DB_NAME-DS_Log.LDF
NULL 102400 KB Unlimited 25600 KB log only
"Absar Ahmad" wrote:
> Please post result of following:
> sp_helpfile
> "Skippy, DOT DBA" wrote:
> > I'm running SQL Server 2000. I have a DB with a logical file name containing
> > a hyphen. When trying to change the size of the db and log files by running
> > the following SQL statement it throws an error stating the file name is not
> > listed in sysfiles:
> >
> > -- Modify db file size and max size
> > alter database DB_NAME_DS
> > MODIFY FILE
> > (
> > NAME = [DB_NAME-DS_DATA],
> > SIZE = 50MB,
> > MAXSIZE = 100MB,
> > FILEGROWTH = 50MB
> > )
> > -- Modify log file size and max size
> > alter database DB_NAME_DS
> > MODIFY FILE
> > (
> > NAME = [DB_NAME-DS_Log],
> > SIZE = 50MB,
> > MAXSIZE = 100MB,
> > FILEGROWTH = 50MB
> > )
> > GO
> >
> > I've verified the logical file names using SP_HELPDB. When the DB was
> > created I did not specify any logical file names. SQL Server assigned them
> > automatically and put the hyphens in. What is causing the problem with the
> > SQL command and how can I get around this problem.
> >
> > Thanks for any help!sql

change ip address and machine name

I need to change the ip address and machine name on a server which I have sql server 7.0 enterprise edition running on. Is there any specifics to be concerned over (sql registration, etc.)
thanksHi,
AFAIK SQL Server Wont bother about the IP Address. Once
you change the machine name of SQL BOx, you have to delete
the previous registration and re-register with the new
server name.
Run SQL Server 7.0 Setup from the Product CD. This will
just update SQL Server Internally to reflect new machine
name. Once you are done with that you need to update the
SQL Server by the below two stored procedures
sp_dropserver <old_name> go
sp_addserver <newname>, local go
Stop and Restart the SQL Service.
HTH
--
Regards
THIRUMAL REDDY MARAM
Sys Admin / SQL Server DBA
>--Original Message--
>I need to change the ip address and machine name on a
server which I have sql server 7.0 enterprise edition
running on. Is there any specifics to be concerned over
(sql registration, etc.)?
>thanks
>.
>sql

Change Database from Sql-Server 2000 to 2005

Hi ,

Currently my application running on Sql-Server 2000 and I want to upgrade with Sql-Server 2005
will It work sql- server 2005?

if no what changed i need to do?
Can anyone help me?

Thanx

Move the thread from Visual Basic General forum. The question is mainly on SQL Server upgrading issue. There are more exports in SQL Server Setup & Upgrade forum, I believe you can get the satisfying answer in this forum.

Thanks again for your question.

|||Yes ! There are 2 types of upgrades available,
1. Inplace upgrade
2. Side by Side upgrade

You can refer these documents,
http://www.microsoft.com/downloads/details.aspx?FamilyID=3d5e96d9-0074-46c4-bd4f-c3eb2abf4b66&DisplayLang=en
http://www.sqlservercentral.com/columnists/ssamuel/2987.asp
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2068912&SiteID=1

In side by side upgrade you can choose any of the following,
1. backup db in sql 2000 and restore it in sql 2005
2. detach db in sql 2000 and attach it in sql 2005
3. copy database wizard

You need to install sql 2005 first and then perform as above

Inplace upgrade will automatically upgrade the existing sql 2000 instance to sql 2005 instance..........but you need to make use of upgrade advisor to check if any errors are reported and then take corrective actions and then proceed........you can go ahead with Inplace upgrade...........

http://msdn2.microsoft.com/en-us/library/ms144256.aspx upgrade advisor
http://msdn2.microsoft.com/en-us/library/ms144245.aspx

Change Database from Sql-Server 2000 to 2005

Hi ,

Currently my application running on Sql-Server 2000 and I want to upgrade with Sql-Server 2005
will It work sql- server 2005?

if no what changed i need to do?
Can anyone help me?

Thanx

Move the thread from Visual Basic General forum. The question is mainly on SQL Server upgrading issue. There are more exports in SQL Server Setup & Upgrade forum, I believe you can get the satisfying answer in this forum.

Thanks again for your question.

|||Yes ! There are 2 types of upgrades available,
1. Inplace upgrade
2. Side by Side upgrade

You can refer these documents,
http://www.microsoft.com/downloads/details.aspx?FamilyID=3d5e96d9-0074-46c4-bd4f-c3eb2abf4b66&DisplayLang=en
http://www.sqlservercentral.com/columnists/ssamuel/2987.asp
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2068912&SiteID=1

In side by side upgrade you can choose any of the following,
1. backup db in sql 2000 and restore it in sql 2005
2. detach db in sql 2000 and attach it in sql 2005
3. copy database wizard

You need to install sql 2005 first and then perform as above

Inplace upgrade will automatically upgrade the existing sql 2000 instance to sql 2005 instance..........but you need to make use of upgrade advisor to check if any errors are reported and then take corrective actions and then proceed........you can go ahead with Inplace upgrade...........

http://msdn2.microsoft.com/en-us/library/ms144256.aspx upgrade advisor
http://msdn2.microsoft.com/en-us/library/ms144245.aspx

Change computer name while running SQL Server

I need to change the computer name on my Windows Server 2003. I am currently
running SQl Server, and I am trying to find out if changing the name will
affect the SQL server or not. Is there a process for doing this, or if I
change the name and reboot, will the name automatically change in the SQL
Server Services Manager?
Thanks,
JOE
"Joe Michl" <JoeMichl@.discussions.microsoft.com> wrote in message
news:094A1C97-34EE-49F2-B374-1BC7DF62A712@.microsoft.com...
> I need to change the computer name on my Windows Server 2003. I am
currently
> running SQl Server, and I am trying to find out if changing the name will
> affect the SQL server or not. Is there a process for doing this, or if I
> change the name and reboot, will the name automatically change in the SQL
> Server Services Manager?
As long as there are no applications coded for your specific server name you
should be OK. After changing the server name and rebooting, use the commands
in the following article:
http://support.microsoft.com/default...&Product=sql2k
Steve

change authentication method in a local database file

I have made a small asp.net project which uses a local database file as a part of the project. The project is running fine om my local machine, but when I upload it to the remote server, the login fails for the server.

I suspect this is can be solved by using sqlserver authentication. But I have now spent a lot of time trying to configure the database file to use this authentication mode. As I see it there are three possible solutions to the problem.

use management studio express to configure the local mdf file (Ecxept that I cant find out how to connect to the mdf-file) and from here change the authentication method to sqlserver authentication. use Visual Web developer to change the authentication method (but how?) make the windows authentication work on the server (this would probably require that mannamgement studio express connects to the remote database. (Same problem as no 1)

Help will be higly appreciated.

Bjarke

hi Bjarke,

to make things run, the account running the application server must be one of the SQL Server registered logins (at the instance level and not database level).. if your solution is hosted, I do think the IT stuff of the hosting company provides that "by default" or nothing would run, and I do not think they will grant you a standard SQL Server login... did you contact them?

regards

|||

Hello Andrea!

Maybe I have misunderstood something?

When you make an asp.net 2.0 project there is a folder (App_Data) where sit is possible via Visual web developer to add a sql database file. As I have understood it this file works as a little database server as well. Does SQL server even have to be installed on the server to make these files work as a database?

I have not contacted the hosting company about this problem, because I didnt consider it a problem. But Maybe I am wrong.

Do you know how to change the autnentication method for a local database file? I have tried a lot of things, and searched the internet for an answer, but without luck.

Thanks Bjarke

|||

hi Bjarke,

bjarke wrote:

Hello Andrea!

Maybe I have misunderstood something?

When you make an asp.net 2.0 project there is a folder (App_Data) where sit is possible via Visual web developer to add a sql database file. As I have understood it this file works as a little database server as well.

perhaps you are intending User Istances "mode", a feature available only with SQLExpress edition of SQL Server 2005.. remember that this feature is not provided by hoster that do not have SQLExpress but "traditional" SQL Server 2005 editions...

Does SQL server even have to be installed on the server to make these files work as a database?

yes, SQLExpress must be installed on the computer running the application, in your case the web application, thus the application server... when the app start's up, a User Instance of SQLExpress is generated (if not already existing)...

I have not contacted the hosting company about this problem, because I didnt consider it a problem. But Maybe I am wrong.

Do you know how to change the autnentication method for a local database file? I have tried a lot of things, and searched the internet for an answer, but without luck.

Thanks Bjarke

the "traditional" authentication "setting" can be changed using SSMSE, accessing the instance property, in the security tab or modifying a Windows registry key.. stay with the first one

the service must be restarted to use the new settings..

as regard user instances, only Windows authentication can be used with this "access mode"...

if you mean how to modify the authentication settings in the connection strings, you can have a look here..

regards

Cell Security : Help

SECURITY USING CELL-SECURITY:

From what i've read cell security s enforced on the client. If someone is able to gain access to a machine running the client (for example an application server or a web server) he is able to get cell values independently of the fact that those values will be defined as #N/A in the secured cell value property. The real value is travelling between theAnalysis Server and the application server. Is this true ? How can we effectively garantee true security ?Did you look in BOL?

Cell Security
In a cube role, you can implement cell security to limit the cube cells that end users in the role can view as they browse cubes. You can also grant read/write access to a write-enabled cube and limit the cells that end users in the role can update. You do this by selecting a policy and by selecting a rule or defining a custom rule for each permission.

Cell security is optional. If you do not specify cell security, end users see all cell values in cubes they are authorized to access. (However, if dimension security is specified, cells for some members might not be viewable.) If a cube is write-enabled, end users cannot update cell values. If one or more of a virtual cube's component cubes are write-enabled, end users cannot update the cell values of virtual cubes.

If a policy or rule permits updates to a cell, it can be updated if it is an atomic cell. If the cell is not atomic, it can be updated only if the client application provides a way of dispersing the update over the subordinate atomic cells. For example, in a client application a write-enabled cube is displayed with the lowest level of every dimension except Time. On the axis for the Time dimension, the nonatomic cells for months are displayed, but the subordinate atomic cells for days are not. (Days is the lowest level in the Time dimension.) A cell for June can be updated by adding $90 if the client application provides a way of dividing the +$90 update into thirty +$3 updates, one to each of the cells for the 30 days in June. Dispersion methods other than simple division can also be used. The UPDATE CUBE statement provides several methods. For more information, see UPDATE CUBE Statement.

Ceate Database Question

Hi,
I am trying to write an installtion sql script that creates a database.
However, I am running into problems because the potential of sql servers
having different setups (log and db files on different partitions...). How
do I handle this?
ThanksHow do you plan to run the script? I have used a batch file that calls
a different creation script for each possible environment. The batch
file handles the branching based on a command line parameter. So the
admin just has to type: "DBSETUP TEST" or "DBSETUP STAGE" the DOS
prompt.
If you want to make the file locations, sizes, etc totally configurable
by a non-DBA type user then I think you'll have to create a
user-interface of some kind.
David Portas
SQL Server MVP
--|||What exactly is the problem? Did you receive any error messages when you
executed the script? Can you post your script here? With CREATE DATABASE
statements, you can include the physical location of the database & log
files irrespective of the partition they are on.
Anith|||David,
Sorry I have been out of town. Is there a way to find out how the SQL
server is configured and change the script accordingly?
Thanks
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1129309258.498992.121140@.g49g2000cwa.googlegroups.com...
> How do you plan to run the script? I have used a batch file that calls
> a different creation script for each possible environment. The batch
> file handles the branching based on a command line parameter. So the
> admin just has to type: "DBSETUP TEST" or "DBSETUP STAGE" the DOS
> prompt.
> If you want to make the file locations, sizes, etc totally configurable
> by a non-DBA type user then I think you'll have to create a
> user-interface of some kind.
> --
> David Portas
> SQL Server MVP
> --
>

CDO mail attachment is not working

Hi
I have some SQL code below which I'm running on a server DEV_TESTSTAGE2 with
Windows 2000 and SQL Server 7. I'm trying to send an attachment which works
fine if the attachment is on DEV_TESTSTAGE2 but if the attachement is
somewhere else on the network it send the email without the attachment and I
get the error message below. I've had a look on the internet but most of the
web peges refer to permissions problems in ASP. In my case I'm using SQL
Server objects and I'm running the query with Windows authentication and my
NT logon id
has access to the file attachement . I've tried running the query using SQL
authentication but I get the same error.
Any help would be gratefully appreciated
-2147024891
Source: CDO.Message.1
Description: Access is denied
declare @.HResult int
declare @.HR int
declare @.iMsg int
declare @.Text varchar(8000)
Declare @.source varchar(255)
Declare @.output varchar(1000)
Declare @.description varchar(500)
declare @.attachfile varchar(1000)
--************* Create the CDO.Message Object ************************
exec @.HResult = sp_OACreate 'CDO.Message', @.iMsg OUT
--error handling for failure to create object......
select @.HResult, @.iMsg
--***************Configuring the Message Object ******************
exec @.HResult = sp_OASetProperty @.iMsg,
'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendus
ing").Value','2'
exec @.HResult = sp_OASetProperty @.iMsg,
'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpse
rver").Value','CHOPGBBES001.gb.cannonsgroup.net'
exec @.HResult = sp_OASetProperty @.iMsg,
'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpse
rverport").Value','25'
exec @.HResult = sp_OASetProperty @.iMsg,
'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpus
essl").Value','False'
exec @.HResult = sp_OASetProperty @.iMsg,
'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpco
nnectiontimeout").Value','60'
-- Save the configurations to the message object.
EXEC @.HResult = sp_OAMethod @.iMsg, 'Configuration.Fields.Update', null
-- Set the e-mail parameters.
EXEC @.HResult = sp_OASetProperty @.iMsg, 'To', 'myemailaddress'
EXEC @.HResult = sp_OASetProperty @.iMsg, 'From', 'TESTSend'
EXEC @.HResult = sp_OASetProperty @.iMsg, 'Subject', 'this is the subject'
EXEC @.HResult = sp_OASetProperty @.iMsg, 'TextBody', 'this is the body'
set @.attachfile = '\\flavius\IT\pstat.txt'
--set @.attachfile = 'c:\boot.ini'
EXEC @.HResult = sp_OAMethod @.iMsg, 'AddAttachment', Null,@.AttachFile
EXEC @.hr = sp_OAGetErrorInfo @.HResult, @.source OUT, @.description OUT
--IF @.hr = 0
-- BEGIN
SELECT @.output = ' Source: ' + @.source
PRINT @.output
SELECT @.output = ' Description: ' + @.description
PRINT @.output
EXEC @.HResult = sp_OAMethod @.iMsg, 'Send', NULL
EXEC @.hr = sp_OAGetErrorInfo @.HResult, @.source OUT, @.description OUT
--IF @.hr = 0
-- BEGIN
SELECT @.output = ' Source: ' + @.source
PRINT @.output
SELECT @.output = ' Description: ' + @.description
PRINT @.output
RonanRonan
Do you have any permissions issue to the folder/file?
in addition ,take a look at
http://www.sqldev.net/xp/xpsmtp.htm
"Ronan" <Ronan@.discussions.microsoft.com> wrote in message
news:767F2973-2782-4574-9940-CA949D740C1F@.microsoft.com...
> Hi
> I have some SQL code below which I'm running on a server DEV_TESTSTAGE2
> with
> Windows 2000 and SQL Server 7. I'm trying to send an attachment which
> works
> fine if the attachment is on DEV_TESTSTAGE2 but if the attachement is
> somewhere else on the network it send the email without the attachment and
> I
> get the error message below. I've had a look on the internet but most of
> the
> web peges refer to permissions problems in ASP. In my case I'm using SQL
> Server objects and I'm running the query with Windows authentication and
> my
> NT logon id
> has access to the file attachement . I've tried running the query using
> SQL
> authentication but I get the same error.
> Any help would be gratefully appreciated
> -2147024891
> Source: CDO.Message.1
> Description: Access is denied
>
> declare @.HResult int
> declare @.HR int
> declare @.iMsg int
> declare @.Text varchar(8000)
> Declare @.source varchar(255)
> Declare @.output varchar(1000)
> Declare @.description varchar(500)
> declare @.attachfile varchar(1000)
>
> --************* Create the CDO.Message Object ************************
> exec @.HResult = sp_OACreate 'CDO.Message', @.iMsg OUT
>
> --error handling for failure to create object......
> select @.HResult, @.iMsg
>
> --***************Configuring the Message Object ******************
> exec @.HResult = sp_OASetProperty @.iMsg,
> 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/send
using").Value','2'
> exec @.HResult = sp_OASetProperty @.iMsg,
> 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtp
server").Value','CHOPGBBES001.gb.cannonsgroup.net'
> exec @.HResult = sp_OASetProperty @.iMsg,
> 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtp
serverport").Value','25'
> exec @.HResult = sp_OASetProperty @.iMsg,
> 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtp
usessl").Value','False'
> exec @.HResult = sp_OASetProperty @.iMsg,
> 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtp
connectiontimeout").Value','60'
>
> -- Save the configurations to the message object.
> EXEC @.HResult = sp_OAMethod @.iMsg, 'Configuration.Fields.Update', null
>
> -- Set the e-mail parameters.
> EXEC @.HResult = sp_OASetProperty @.iMsg, 'To', 'myemailaddress'
> EXEC @.HResult = sp_OASetProperty @.iMsg, 'From', 'TESTSend'
> EXEC @.HResult = sp_OASetProperty @.iMsg, 'Subject', 'this is the subject'
> EXEC @.HResult = sp_OASetProperty @.iMsg, 'TextBody', 'this is the body'
> set @.attachfile = '\\flavius\IT\pstat.txt'
> --set @.attachfile = 'c:\boot.ini'
> EXEC @.HResult = sp_OAMethod @.iMsg, 'AddAttachment', Null,@.AttachFile
>
> EXEC @.hr = sp_OAGetErrorInfo @.HResult, @.source OUT, @.description OUT
> --IF @.hr = 0
> -- BEGIN
> SELECT @.output = ' Source: ' + @.source
> PRINT @.output
> SELECT @.output = ' Description: ' + @.description
> PRINT @.output
>
> EXEC @.HResult = sp_OAMethod @.iMsg, 'Send', NULL
>
> EXEC @.hr = sp_OAGetErrorInfo @.HResult, @.source OUT, @.description OUT
> --IF @.hr = 0
> -- BEGIN
> SELECT @.output = ' Source: ' + @.source
> PRINT @.output
> SELECT @.output = ' Description: ' + @.description
> PRINT @.output
>
>
> --
> Ronan|||Hi Uri
Yes I should have mentioned I do have access to the file...it has 'Everyone'
permission
I had a look at the article you sent me...it refers to xp_smtp_sendmail...I
can't find that extended stored procedure in the master database of SQL
Server 2000...I have xp_sendmail but that requires you set up
a MAPI client which I don't want to do I want to send it via SMTP
Ronan
"Uri Dimant" wrote:

> Ronan
> Do you have any permissions issue to the folder/file?
> in addition ,take a look at
> http://www.sqldev.net/xp/xpsmtp.htm
>
>
> "Ronan" <Ronan@.discussions.microsoft.com> wrote in message
> news:767F2973-2782-4574-9940-CA949D740C1F@.microsoft.com...
>
>|||I have used the xp_smtpsendmail extended stored procedure. It works well,
but you will have to download the .ddl ..google it, and you'll find it. I've
used it to send reports generated as text files to business managers in the
past with great success...you will have to copy it to your sql\bin folder,
and registewr it inside sql, to get it to work...easy to do.
"Ronan" wrote:
> Hi Uri
> Yes I should have mentioned I do have access to the file...it has 'Everyon
e'
> permission
> I had a look at the article you sent me...it refers to xp_smtp_sendmail...
I
> can't find that extended stored procedure in the master database of SQL
> Server 2000...I have xp_sendmail but that requires you set up
> a MAPI client which I don't want to do I want to send it via SMTP
> --
> Ronan
>
> "Uri Dimant" wrote:
>|||Hi Tom
I downloaded and installed xp_smtp_sendmail and again it works fine for
attachements
which are on the machine where xp_smtp_sendmail is registered but it
still doesn't seem
to work when the attachment is on a different machine. I think I'll just
have to put my attachments
on the machine where xp_smtp_sendmail is registered as a work around.
Thanks for your help.
Regards
Ronan
Ronan
"Tom Mongold" wrote:
> I have used the xp_smtpsendmail extended stored procedure. It works well,
> but you will have to download the .ddl ..google it, and you'll find it. I'
ve
> used it to send reports generated as text files to business managers in th
e
> past with great success...you will have to copy it to your sql\bin folder
,
> and registewr it inside sql, to get it to work...easy to do.
> "Ronan" wrote:
>

Causes of allocation errors in tempdb

Hello,
Our company produces some software using SQL 7.0 as a back end, one function
of which is the running of several reports overnight. These reports make
use of temporary tables which can cause tempdb to grow to a few hundred
megabytes.
On a handful of our customers' servers, we have seen massive growth in
tempdb (usually consuming all available disk space - several Gb) when
running these reports. On running DBCC CHECKDB against tempdb we have
noticed thousands of allocation errors not associated with any particular
object. I have included details of some of these errors later on with some
commentary.
The first thought has been physical disk errors. All our customers' servers
run on either RAID1 or RAID5 arrays and we have run chkdsk with no errors
found. There have been no errors in the Windows event log suggesting
impending disk failure either.
The other databases (including system databases) have no errors when checked
with DBCC CHECKDB.
If there are any other factors that could lead to this situation I'd
appreciate it if you could let me know.
Many thanks,
John McLusky.
Could not allocate space for object '(SYSTEM table id: -996385922)' in
database 'TEMPDB' because the 'DEFAULT' filegroup is full.

> 1. When run on this system, the temporary database had grown so large that
> there was no room for further expansion.
Server: Msg 8999, Level 16, State 1, Line 1
Database tempdb allocation errors prevent further CHECKDB processing.

> 2. I suspect that this is related to the errors that follow shortly!
Server: Msg 1101, Level 17, State 1, Line 1
Could not allocate new page for database 'tempdb'. There are no more pages
available in filegroup DEFAULT. Space can be created by dropping objects,
adding additional files, or allowing file growth.

> 3. as number 1.
Server: Msg 8906, Level 16, State 1, Line 1
Page (1:116) in database ID 2 is allocated in the SGAM (1:3) and PFS (1:1),
but was not allocated in any IAM. PFS flags 'IAM_PG MIXED_EXT ALLOCATED
0_PCT_FULL'.
Server: Msg 8906, Level 16, State 1, Line 1
Page (1:197) in database ID 2 is allocated in the SGAM (1:3) and PFS (1:1),
but was not allocated in any IAM. PFS flags 'IAM_PG MIXED_EXT ALLOCATED
0_PCT_FULL'.
Server: Msg 8906, Level 16, State 1, Line 1
Page (1:198) in database ID 2 is allocated in the SGAM (1:3) and PFS (1:1),
but was not allocated in any IAM. PFS flags 'IAM_PG MIXED_EXT ALLOCATED
0_PCT_FULL'.
Server: Msg 8906, Level 16, State 1, Line 1
Page (1:208) in database ID 2 is allocated in the SGAM (1:3) and PFS (1:1),
but was not allocated in any IAM. PFS flags 'IAM_PG MIXED_EXT ALLOCATED
0_PCT_FULL'.
Server: Msg 8906, Level 16, State 1, Line 1

> 4. Several more otherwise identical errors with different page numbers
> follow. The SGAM and PFS IDs are identical.
Server: Msg 8905, Level 16, State 1, Line 1
Extent (1:120) in database ID 2 is marked allocated in the GAM, but no SGAM
or IAM has allocated it.
Server: Msg 8905, Level 16, State 1, Line 1
Extent (1:128) in database ID 2 is marked allocated in the GAM, but no SGAM
or IAM has allocated it.
Server: Msg 8905, Level 16, State 1, Line 1
Extent (1:136) in database ID 2 is marked allocated in the GAM, but no SGAM
or IAM has allocated it.
Server: Msg 8905, Level 16, State 1, Line 1
Extent (1:144) in database ID 2 is marked allocated in the GAM, but no SGAM
or IAM has allocated it.

> 5. Again, many more of these with only the extent number varying.
Server: Msg 8986, Level 16, State 1, Line 1
Too many errors found (201) for object ID 0. To see all error messages rerun
the statement using "WITH ALL_ERRORMSGS".
CHECKDB found 28208 allocation errors and 0 consistency errors not
associated with any single object.
CHECKDB found 28208 allocation errors and 0 consistency errors in database
'tempdb'.
repair_allow_data_loss is the minimum repair level for the errors found by
DBCC CHECKDB (tempdb ).
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
Hi John ,
From your descriptions, I understood that your tempdb is growing unexpected
and allocation errors with DBCC CHECKDB. Have I understood you? Correct me
if I was wrong.
First of all, have you upgraded your SQL Server to latest update?
Then, you could perform stop SQL Server services, delete existing
TEMPDB.MDF and TEMPDBLOG.LDF and restart SQL Server. Tempdb is recreated
every time service is restarted.
Run dbcc checkdb and dbcc newalloc on tempdb, let me know whether it is
clean now.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Hi Michael,
You're correct.
We have tried recreating tempdb by stopping SQL, deleting the files and then
restarting. Unfortunately once the nightly reports run again the same
problem recurs.
I am fairly sure that SQL 7 is on the latest service pack, but this will be
checked on Monday.
Before the reports run, DBCC CHECKDB comes back clean, but we haven't tried
DBCC NEWALLOC. Although I am out of the office next week my colleagues are
monitoring this thread and will try your advice.
Thanks,
John.
Michael Cheng [MSFT] wrote:
> Hi John ,
> From your descriptions, I understood that your tempdb is growing
> unexpected and allocation errors with DBCC CHECKDB. Have I understood
> you? Correct me if I was wrong.
> First of all, have you upgraded your SQL Server to latest update?
> Then, you could perform stop SQL Server services, delete existing
> TEMPDB.MDF and TEMPDBLOG.LDF and restart SQL Server. Tempdb is
> recreated every time service is restarted.
> Run dbcc checkdb and dbcc newalloc on tempdb, let me know whether it
> is clean now.
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader
> so that others may learn and benefit from your issue.
> ================================================== ===
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
|||Please increase the tempdb size so that you do not run out of space when
running the big reports. You can let tempdb auto grow or preallocate a large
size (the preferred method for high performance).
The error "Could not allocate space for object '(SYSTEM table
id: -996385922)' in
database 'TEMPDB' because the 'DEFAULT' filegroup is full." indicates that
you need to increase space in tempdb.
When you have enough space, the problem probably will go away. If not,
please contact microsoft tech support.
DBCC checkdb (tempdb) itself needs more space in tempdb. So you need to have
enough space as well.
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://weblogs.asp.net/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"John McLusky" <jmclusky@.community.nospam> wrote in message
news:e7buAAWIFHA.3928@.TK2MSFTNGP09.phx.gbl...
> Hi Michael,
> You're correct.
> We have tried recreating tempdb by stopping SQL, deleting the files and
> then restarting. Unfortunately once the nightly reports run again the
> same problem recurs.
> I am fairly sure that SQL 7 is on the latest service pack, but this will
> be checked on Monday.
> Before the reports run, DBCC CHECKDB comes back clean, but we haven't
> tried DBCC NEWALLOC. Although I am out of the office next week my
> colleagues are monitoring this thread and will try your advice.
> Thanks,
> John.
>
> Michael Cheng [MSFT] wrote:
>

Causes of allocation errors in tempdb

Hello,
Our company produces some software using SQL 7.0 as a back end, one function
of which is the running of several reports overnight. These reports make
use of temporary tables which can cause tempdb to grow to a few hundred
megabytes.
On a handful of our customers' servers, we have seen massive growth in
tempdb (usually consuming all available disk space - several Gb) when
running these reports. On running DBCC CHECKDB against tempdb we have
noticed thousands of allocation errors not associated with any particular
object. I have included details of some of these errors later on with some
commentary.
The first thought has been physical disk errors. All our customers' servers
run on either RAID1 or RAID5 arrays and we have run chkdsk with no errors
found. There have been no errors in the Windows event log suggesting
impending disk failure either.
The other databases (including system databases) have no errors when checked
with DBCC CHECKDB.
If there are any other factors that could lead to this situation I'd
appreciate it if you could let me know.
Many thanks,
John McLusky.
Could not allocate space for object '(SYSTEM table id: -996385922)' in
database 'TEMPDB' because the 'DEFAULT' filegroup is full.

> 1. When run on this system, the temporary database had grown so large that
> there was no room for further expansion.
Server: Msg 8999, Level 16, State 1, Line 1
Database tempdb allocation errors prevent further CHECKDB processing.

> 2. I suspect that this is related to the errors that follow shortly!
Server: Msg 1101, Level 17, State 1, Line 1
Could not allocate new page for database 'tempdb'. There are no more pages
available in filegroup DEFAULT. Space can be created by dropping objects,
adding additional files, or allowing file growth.

> 3. as number 1.
Server: Msg 8906, Level 16, State 1, Line 1
Page (1:116) in database ID 2 is allocated in the SGAM (1:3) and PFS (1:1),
but was not allocated in any IAM. PFS flags 'IAM_PG MIXED_EXT ALLOCATED
0_PCT_FULL'.
Server: Msg 8906, Level 16, State 1, Line 1
Page (1:197) in database ID 2 is allocated in the SGAM (1:3) and PFS (1:1),
but was not allocated in any IAM. PFS flags 'IAM_PG MIXED_EXT ALLOCATED
0_PCT_FULL'.
Server: Msg 8906, Level 16, State 1, Line 1
Page (1:198) in database ID 2 is allocated in the SGAM (1:3) and PFS (1:1),
but was not allocated in any IAM. PFS flags 'IAM_PG MIXED_EXT ALLOCATED
0_PCT_FULL'.
Server: Msg 8906, Level 16, State 1, Line 1
Page (1:208) in database ID 2 is allocated in the SGAM (1:3) and PFS (1:1),
but was not allocated in any IAM. PFS flags 'IAM_PG MIXED_EXT ALLOCATED
0_PCT_FULL'.
Server: Msg 8906, Level 16, State 1, Line 1

> 4. Several more otherwise identical errors with different page numbers
> follow. The SGAM and PFS IDs are identical.
Server: Msg 8905, Level 16, State 1, Line 1
Extent (1:120) in database ID 2 is marked allocated in the GAM, but no SGAM
or IAM has allocated it.
Server: Msg 8905, Level 16, State 1, Line 1
Extent (1:128) in database ID 2 is marked allocated in the GAM, but no SGAM
or IAM has allocated it.
Server: Msg 8905, Level 16, State 1, Line 1
Extent (1:136) in database ID 2 is marked allocated in the GAM, but no SGAM
or IAM has allocated it.
Server: Msg 8905, Level 16, State 1, Line 1
Extent (1:144) in database ID 2 is marked allocated in the GAM, but no SGAM
or IAM has allocated it.

> 5. Again, many more of these with only the extent number varying.
Server: Msg 8986, Level 16, State 1, Line 1
Too many errors found (201) for object ID 0. To see all error messages rerun
the statement using "WITH ALL_ERRORMSGS".
CHECKDB found 28208 allocation errors and 0 consistency errors not
associated with any single object.
CHECKDB found 28208 allocation errors and 0 consistency errors in database
'tempdb'.
repair_allow_data_loss is the minimum repair level for the errors found by
DBCC CHECKDB (tempdb ).
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.Hi John ,
From your descriptions, I understood that your tempdb is growing unexpected
and allocation errors with DBCC CHECKDB. Have I understood you? Correct me
if I was wrong.
First of all, have you upgraded your SQL Server to latest update?
Then, you could perform stop SQL Server services, delete existing
TEMPDB.MDF and TEMPDBLOG.LDF and restart SQL Server. Tempdb is recreated
every time service is restarted.
Run dbcc checkdb and dbcc newalloc on tempdb, let me know whether it is
clean now.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Michael,
You're correct.
We have tried recreating tempdb by stopping SQL, deleting the files and then
restarting. Unfortunately once the nightly reports run again the same
problem recurs.
I am fairly sure that SQL 7 is on the latest service pack, but this will be
checked on Monday.
Before the reports run, DBCC CHECKDB comes back clean, but we haven't tried
DBCC NEWALLOC. Although I am out of the office next week my colleagues are
monitoring this thread and will try your advice.
Thanks,
John.
Michael Cheng [MSFT] wrote:
> Hi John ,
> From your descriptions, I understood that your tempdb is growing
> unexpected and allocation errors with DBCC CHECKDB. Have I understood
> you? Correct me if I was wrong.
> First of all, have you upgraded your SQL Server to latest update?
> Then, you could perform stop SQL Server services, delete existing
> TEMPDB.MDF and TEMPDBLOG.LDF and restart SQL Server. Tempdb is
> recreated every time service is restarted.
> Run dbcc checkdb and dbcc newalloc on tempdb, let me know whether it
> is clean now.
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader
> so that others may learn and benefit from your issue.
> ========================================
=============
> This posting is provided "AS IS" with no warranties, and confers no
> rights.|||Please increase the tempdb size so that you do not run out of space when
running the big reports. You can let tempdb auto grow or preallocate a large
size (the preferred method for high performance).
The error "Could not allocate space for object '(SYSTEM table
id: -996385922)' in
database 'TEMPDB' because the 'DEFAULT' filegroup is full." indicates that
you need to increase space in tempdb.
When you have enough space, the problem probably will go away. If not,
please contact microsoft tech support.
DBCC checkdb (tempdb) itself needs more space in tempdb. So you need to have
enough space as well.
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://weblogs.asp.net/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"John McLusky" <jmclusky@.community.nospam> wrote in message
news:e7buAAWIFHA.3928@.TK2MSFTNGP09.phx.gbl...
> Hi Michael,
> You're correct.
> We have tried recreating tempdb by stopping SQL, deleting the files and
> then restarting. Unfortunately once the nightly reports run again the
> same problem recurs.
> I am fairly sure that SQL 7 is on the latest service pack, but this will
> be checked on Monday.
> Before the reports run, DBCC CHECKDB comes back clean, but we haven't
> tried DBCC NEWALLOC. Although I am out of the office next week my
> colleagues are monitoring this thread and will try your advice.
> Thanks,
> John.
>
> Michael Cheng [MSFT] wrote:
>

Causes of allocation errors in tempdb

Hello,
Our company produces some software using SQL 7.0 as a back end, one function
of which is the running of several reports overnight. These reports make
use of temporary tables which can cause tempdb to grow to a few hundred
megabytes.
On a handful of our customers' servers, we have seen massive growth in
tempdb (usually consuming all available disk space - several Gb) when
running these reports. On running DBCC CHECKDB against tempdb we have
noticed thousands of allocation errors not associated with any particular
object. I have included details of some of these errors later on with some
commentary.
The first thought has been physical disk errors. All our customers' servers
run on either RAID1 or RAID5 arrays and we have run chkdsk with no errors
found. There have been no errors in the Windows event log suggesting
impending disk failure either.
The other databases (including system databases) have no errors when checked
with DBCC CHECKDB.
If there are any other factors that could lead to this situation I'd
appreciate it if you could let me know.
Many thanks,
John McLusky.
Could not allocate space for object '(SYSTEM table id: -996385922)' in
database 'TEMPDB' because the 'DEFAULT' filegroup is full.
> 1. When run on this system, the temporary database had grown so large that
> there was no room for further expansion.
Server: Msg 8999, Level 16, State 1, Line 1
Database tempdb allocation errors prevent further CHECKDB processing.
> 2. I suspect that this is related to the errors that follow shortly!
Server: Msg 1101, Level 17, State 1, Line 1
Could not allocate new page for database 'tempdb'. There are no more pages
available in filegroup DEFAULT. Space can be created by dropping objects,
adding additional files, or allowing file growth.
> 3. as number 1.
Server: Msg 8906, Level 16, State 1, Line 1
Page (1:116) in database ID 2 is allocated in the SGAM (1:3) and PFS (1:1),
but was not allocated in any IAM. PFS flags 'IAM_PG MIXED_EXT ALLOCATED
0_PCT_FULL'.
Server: Msg 8906, Level 16, State 1, Line 1
Page (1:197) in database ID 2 is allocated in the SGAM (1:3) and PFS (1:1),
but was not allocated in any IAM. PFS flags 'IAM_PG MIXED_EXT ALLOCATED
0_PCT_FULL'.
Server: Msg 8906, Level 16, State 1, Line 1
Page (1:198) in database ID 2 is allocated in the SGAM (1:3) and PFS (1:1),
but was not allocated in any IAM. PFS flags 'IAM_PG MIXED_EXT ALLOCATED
0_PCT_FULL'.
Server: Msg 8906, Level 16, State 1, Line 1
Page (1:208) in database ID 2 is allocated in the SGAM (1:3) and PFS (1:1),
but was not allocated in any IAM. PFS flags 'IAM_PG MIXED_EXT ALLOCATED
0_PCT_FULL'.
Server: Msg 8906, Level 16, State 1, Line 1
> 4. Several more otherwise identical errors with different page numbers
> follow. The SGAM and PFS IDs are identical.
Server: Msg 8905, Level 16, State 1, Line 1
Extent (1:120) in database ID 2 is marked allocated in the GAM, but no SGAM
or IAM has allocated it.
Server: Msg 8905, Level 16, State 1, Line 1
Extent (1:128) in database ID 2 is marked allocated in the GAM, but no SGAM
or IAM has allocated it.
Server: Msg 8905, Level 16, State 1, Line 1
Extent (1:136) in database ID 2 is marked allocated in the GAM, but no SGAM
or IAM has allocated it.
Server: Msg 8905, Level 16, State 1, Line 1
Extent (1:144) in database ID 2 is marked allocated in the GAM, but no SGAM
or IAM has allocated it.
> 5. Again, many more of these with only the extent number varying.
Server: Msg 8986, Level 16, State 1, Line 1
Too many errors found (201) for object ID 0. To see all error messages rerun
the statement using "WITH ALL_ERRORMSGS".
CHECKDB found 28208 allocation errors and 0 consistency errors not
associated with any single object.
CHECKDB found 28208 allocation errors and 0 consistency errors in database
'tempdb'.
repair_allow_data_loss is the minimum repair level for the errors found by
DBCC CHECKDB (tempdb ).
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.Hi John ,
From your descriptions, I understood that your tempdb is growing unexpected
and allocation errors with DBCC CHECKDB. Have I understood you? Correct me
if I was wrong.
First of all, have you upgraded your SQL Server to latest update?
Then, you could perform stop SQL Server services, delete existing
TEMPDB.MDF and TEMPDBLOG.LDF and restart SQL Server. Tempdb is recreated
every time service is restarted.
Run dbcc checkdb and dbcc newalloc on tempdb, let me know whether it is
clean now.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Michael,
You're correct.
We have tried recreating tempdb by stopping SQL, deleting the files and then
restarting. Unfortunately once the nightly reports run again the same
problem recurs.
I am fairly sure that SQL 7 is on the latest service pack, but this will be
checked on Monday.
Before the reports run, DBCC CHECKDB comes back clean, but we haven't tried
DBCC NEWALLOC. Although I am out of the office next week my colleagues are
monitoring this thread and will try your advice.
Thanks,
John.
Michael Cheng [MSFT] wrote:
> Hi John ,
> From your descriptions, I understood that your tempdb is growing
> unexpected and allocation errors with DBCC CHECKDB. Have I understood
> you? Correct me if I was wrong.
> First of all, have you upgraded your SQL Server to latest update?
> Then, you could perform stop SQL Server services, delete existing
> TEMPDB.MDF and TEMPDBLOG.LDF and restart SQL Server. Tempdb is
> recreated every time service is restarted.
> Run dbcc checkdb and dbcc newalloc on tempdb, let me know whether it
> is clean now.
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader
> so that others may learn and benefit from your issue.
> =====================================================> This posting is provided "AS IS" with no warranties, and confers no
> rights.|||Please increase the tempdb size so that you do not run out of space when
running the big reports. You can let tempdb auto grow or preallocate a large
size (the preferred method for high performance).
The error "Could not allocate space for object '(SYSTEM table
id: -996385922)' in
database 'TEMPDB' because the 'DEFAULT' filegroup is full." indicates that
you need to increase space in tempdb.
When you have enough space, the problem probably will go away. If not,
please contact microsoft tech support.
DBCC checkdb (tempdb) itself needs more space in tempdb. So you need to have
enough space as well.
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://weblogs.asp.net/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"John McLusky" <jmclusky@.community.nospam> wrote in message
news:e7buAAWIFHA.3928@.TK2MSFTNGP09.phx.gbl...
> Hi Michael,
> You're correct.
> We have tried recreating tempdb by stopping SQL, deleting the files and
> then restarting. Unfortunately once the nightly reports run again the
> same problem recurs.
> I am fairly sure that SQL 7 is on the latest service pack, but this will
> be checked on Monday.
> Before the reports run, DBCC CHECKDB comes back clean, but we haven't
> tried DBCC NEWALLOC. Although I am out of the office next week my
> colleagues are monitoring this thread and will try your advice.
> Thanks,
> John.
>
> Michael Cheng [MSFT] wrote:
>> Hi John ,
>> From your descriptions, I understood that your tempdb is growing
>> unexpected and allocation errors with DBCC CHECKDB. Have I understood
>> you? Correct me if I was wrong.
>> First of all, have you upgraded your SQL Server to latest update?
>> Then, you could perform stop SQL Server services, delete existing
>> TEMPDB.MDF and TEMPDBLOG.LDF and restart SQL Server. Tempdb is
>> recreated every time service is restarted.
>> Run dbcc checkdb and dbcc newalloc on tempdb, let me know whether it
>> is clean now.
>>
>> Sincerely yours,
>> Michael Cheng
>> Microsoft Online Partner Support
>> When responding to posts, please "Reply to Group" via your newsreader
>> so that others may learn and benefit from your issue.
>> =====================================================>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>