Data File Used Space

How is it, that when I delete records in my Test environment that the "data
used" within the data file decreases, but not so when I delete records from
my production environment?
Message posted via http://www.droptable.com
See my reply to your other message.
Andrew J. Kelly SQL MVP
"Robert Richards via droptable.com" <forum@.nospam.droptable.com> wrote in
message news:2cc2a3ac2e7643468a1c4107212c0865@.droptable.co m...
> How is it, that when I delete records in my Test environment that the
> "data
> used" within the data file decreases, but not so when I delete records
> from
> my production environment?
> --
> Message posted via http://www.droptable.com
|||hi
why dont you post it again so that others can go through the post aswell
best Regards,
Chandra
http://groups.msn.com/SQLResource/
http://chanduas.blogspot.com/
*** Sent via Developersdex http://www.codecomments.com ***

Data File Used Space

How is it, that when I delete records in my Test environment that the "data
used" within the data file decreases, but not so when I delete records from
my production environment?
Message posted via http://www.droptable.comSee my reply to your other message.
Andrew J. Kelly SQL MVP
"Robert Richards via droptable.com" <forum@.nospam.droptable.com> wrote in
message news:2cc2a3ac2e7643468a1c4107212c0865@.SQ
droptable.com...
> How is it, that when I delete records in my Test environment that the
> "data
> used" within the data file decreases, but not so when I delete records
> from
> my production environment?
> --
> Message posted via http://www.droptable.com|||hi
why dont you post it again so that others can go through the post aswell
best Regards,
Chandra
http://groups.msn.com/SQLResource/
http://chanduas.blogspot.com/
---
*** Sent via Developersdex http://www.codecomments.com ***sql

Data File Used Space

How is it, that when I delete records in my Test environment that the "data
used" within the data file decreases, but not so when I delete records from
my production environment?
--
Message posted via http://www.sqlmonster.comSee my reply to your other message.
--
Andrew J. Kelly SQL MVP
"Robert Richards via SQLMonster.com" <forum@.nospam.SQLMonster.com> wrote in
message news:2cc2a3ac2e7643468a1c4107212c0865@.SQLMonster.com...
> How is it, that when I delete records in my Test environment that the
> "data
> used" within the data file decreases, but not so when I delete records
> from
> my production environment?
> --
> Message posted via http://www.sqlmonster.com

data deletion

How Is it possible to delete data from table leaving the tables intact?If you mean all the data you can use DELETE by itself or if you don't have
any referential integrity you can use TRUNCATE TABLE. I would advise making
sure you have valid backups first.
Andrew J. Kelly SQL MVP
"albren" <anonymous@.discussions.microsoft.com> wrote in message
news:CC67A65A-1AE4-4B82-9388-9DF0FE58A3CE@.microsoft.com...
> How Is it possible to delete data from table leaving the tables intact?|||Hi,
2 options,
1. Delete with out a condition
eg: DELETE from <tablename> or DELETE <Tablename>
2. Truncate command(This will be much faster bacause it is a non logged
operation)
eg: TRUNCATE table <tablename>
Thanks
Hari
MCDBA
"albren" <anonymous@.discussions.microsoft.com> wrote in message
news:CC67A65A-1AE4-4B82-9388-9DF0FE58A3CE@.microsoft.com...
> How Is it possible to delete data from table leaving the tables intact?

data deletion

How Is it possible to delete data from table leaving the tables intact?If you mean all the data you can use DELETE by itself or if you don't have
any referential integrity you can use TRUNCATE TABLE. I would advise making
sure you have valid backups first.
--
Andrew J. Kelly SQL MVP
"albren" <anonymous@.discussions.microsoft.com> wrote in message
news:CC67A65A-1AE4-4B82-9388-9DF0FE58A3CE@.microsoft.com...
> How Is it possible to delete data from table leaving the tables intact?|||Hi,
2 options,
1. Delete with out a condition
eg: DELETE from <tablename> or DELETE <Tablename>
2. Truncate command(This will be much faster bacause it is a non logged
operation)
eg: TRUNCATE table <tablename>
Thanks
Hari
MCDBA
"albren" <anonymous@.discussions.microsoft.com> wrote in message
news:CC67A65A-1AE4-4B82-9388-9DF0FE58A3CE@.microsoft.com...
> How Is it possible to delete data from table leaving the tables intact?

Data base File is suspect

My server crashed and when it came back it has database marked as suspect, I
detached the database , delete the log file and try to attach it again using
enterprise manager however it saying it can't do it.
I have no backup of log or database for the day our backup was also not
working.
What I can do to recover the database.
Thanks for the help
TanweerHi,
Why did you delete the transaction log file before analyzing the cause for
suspect? I feel that cause for the suspect is because some
file (MDF or LDF) was using by some other process (backup or anti virus)
during startup. This would have been easily resolved by
running the system proc sp_resetstatus. Now since you deleted the LDF file
from query analyzer you could try sp_attach_single_file_db (see books online
for usage). If this fails then:-
You could try the below steps to make your database online using MDF file
only. Since in this processes the LDF file willnot be used on startup
(Emergency mode) , the data integrity might be an issue. (This step can be
used if you do not have any backups). Once the database become online move
the objects to a new database using DTS.
Steps:
1. Create a new database with the same name and same MDF and LDF files
2. Stop sql server and rename the existing MDF to a new one and copy the
original MDF to this location and delete the LDF files.
3. Start SQL Server
4. Now your database will be marked suspect
5. Update the sysdatabases to update to Emergency mode. This will not use
LOG files in start up
Sp_configure "allow updates", 1
go
Reconfigure with override
GO
Update sysdatabases set status = 32768 where name = "BadDbName"
go
Sp_configure "allow updates", 0
go
Reconfigure with override
GO
6. Restart sql server. now the database will be in emergency mode
7. Create a new database and use DTS to copy the objects and data to new
database.
You can use this new database.
Note:
If you have the backup file, it is always recommended to use the backup file
to restore the database. SO that data integrity will be maintained.
.
Thanks
Hari
SQL Server MVP
"Tanweer" <Tanweer@.discussions.microsoft.com> wrote in message
news:F86755A1-3C2A-4CC8-9463-F2E4E5E5F7D6@.microsoft.com...
> My server crashed and when it came back it has database marked as suspect,
> I
> detached the database , delete the log file and try to attach it again
> using
> enterprise manager however it saying it can't do it.
> I have no backup of log or database for the day our backup was also not
> working.
> What I can do to recover the database.
>
> Thanks for the help
> Tanweer|||Have a look here:
http://www.karaszi.com/SQLServer/in..._suspect_db.asp
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Tanweer" <Tanweer@.discussions.microsoft.com> schrieb im Newsbeitrag
news:F86755A1-3C2A-4CC8-9463-F2E4E5E5F7D6@.microsoft.com...
> My server crashed and when it came back it has database marked as suspect,
> I
> detached the database , delete the log file and try to attach it again
> using
> enterprise manager however it saying it can't do it.
> I have no backup of log or database for the day our backup was also not
> working.
> What I can do to recover the database.
>
> Thanks for the help
> Tanweer

Data base File is suspect

My server crashed and when it came back it has database marked as suspect, I
detached the database , delete the log file and try to attach it again using
enterprise manager however it saying it can't do it.
I have no backup of log or database for the day our backup was also not
working.
What I can do to recover the database.
Thanks for the help
Tanweer
Hi,
Why did you delete the transaction log file before analyzing the cause for
suspect? I feel that cause for the suspect is because some
file (MDF or LDF) was using by some other process (backup or anti virus)
during startup. This would have been easily resolved by
running the system proc sp_resetstatus. Now since you deleted the LDF file
from query analyzer you could try sp_attach_single_file_db (see books online
for usage). If this fails then:-
You could try the below steps to make your database online using MDF file
only. Since in this processes the LDF file willnot be used on startup
(Emergency mode) , the data integrity might be an issue. (This step can be
used if you do not have any backups). Once the database become online move
the objects to a new database using DTS.
Steps:
1. Create a new database with the same name and same MDF and LDF files
2. Stop sql server and rename the existing MDF to a new one and copy the
original MDF to this location and delete the LDF files.
3. Start SQL Server
4. Now your database will be marked suspect
5. Update the sysdatabases to update to Emergency mode. This will not use
LOG files in start up
Sp_configure "allow updates", 1
go
Reconfigure with override
GO
Update sysdatabases set status = 32768 where name = "BadDbName"
go
Sp_configure "allow updates", 0
go
Reconfigure with override
GO
6. Restart sql server. now the database will be in emergency mode
7. Create a new database and use DTS to copy the objects and data to new
database.
You can use this new database.
Note:
If you have the backup file, it is always recommended to use the backup file
to restore the database. SO that data integrity will be maintained.
..
Thanks
Hari
SQL Server MVP
"Tanweer" <Tanweer@.discussions.microsoft.com> wrote in message
news:F86755A1-3C2A-4CC8-9463-F2E4E5E5F7D6@.microsoft.com...
> My server crashed and when it came back it has database marked as suspect,
> I
> detached the database , delete the log file and try to attach it again
> using
> enterprise manager however it saying it can't do it.
> I have no backup of log or database for the day our backup was also not
> working.
> What I can do to recover the database.
>
> Thanks for the help
> Tanweer
|||Have a look here:
http://www.karaszi.com/SQLServer/inf...suspect_db.asp
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"Tanweer" <Tanweer@.discussions.microsoft.com> schrieb im Newsbeitrag
news:F86755A1-3C2A-4CC8-9463-F2E4E5E5F7D6@.microsoft.com...
> My server crashed and when it came back it has database marked as suspect,
> I
> detached the database , delete the log file and try to attach it again
> using
> enterprise manager however it saying it can't do it.
> I have no backup of log or database for the day our backup was also not
> working.
> What I can do to recover the database.
>
> Thanks for the help
> Tanweer

Data base File is suspect

My server crashed and when it came back it has database marked as suspect, I
detached the database , delete the log file and try to attach it again using
enterprise manager however it saying it can't do it.
I have no backup of log or database for the day our backup was also not
working.
What I can do to recover the database.
Thanks for the help
TanweerHi,
Why did you delete the transaction log file before analyzing the cause for
suspect? I feel that cause for the suspect is because some
file (MDF or LDF) was using by some other process (backup or anti virus)
during startup. This would have been easily resolved by
running the system proc sp_resetstatus. Now since you deleted the LDF file
from query analyzer you could try sp_attach_single_file_db (see books online
for usage). If this fails then:-
You could try the below steps to make your database online using MDF file
only. Since in this processes the LDF file willnot be used on startup
(Emergency mode) , the data integrity might be an issue. (This step can be
used if you do not have any backups). Once the database become online move
the objects to a new database using DTS.
Steps:
1. Create a new database with the same name and same MDF and LDF files
2. Stop sql server and rename the existing MDF to a new one and copy the
original MDF to this location and delete the LDF files.
3. Start SQL Server
4. Now your database will be marked suspect
5. Update the sysdatabases to update to Emergency mode. This will not use
LOG files in start up
Sp_configure "allow updates", 1
go
Reconfigure with override
GO
Update sysdatabases set status = 32768 where name = "BadDbName"
go
Sp_configure "allow updates", 0
go
Reconfigure with override
GO
6. Restart sql server. now the database will be in emergency mode
7. Create a new database and use DTS to copy the objects and data to new
database.
You can use this new database.
Note:
If you have the backup file, it is always recommended to use the backup file
to restore the database. SO that data integrity will be maintained.
.
Thanks
Hari
SQL Server MVP
"Tanweer" <Tanweer@.discussions.microsoft.com> wrote in message
news:F86755A1-3C2A-4CC8-9463-F2E4E5E5F7D6@.microsoft.com...
> My server crashed and when it came back it has database marked as suspect,
> I
> detached the database , delete the log file and try to attach it again
> using
> enterprise manager however it saying it can't do it.
> I have no backup of log or database for the day our backup was also not
> working.
> What I can do to recover the database.
>
> Thanks for the help
> Tanweer|||Have a look here:
http://www.karaszi.com/SQLServer/info_corrupt_suspect_db.asp
--
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"Tanweer" <Tanweer@.discussions.microsoft.com> schrieb im Newsbeitrag
news:F86755A1-3C2A-4CC8-9463-F2E4E5E5F7D6@.microsoft.com...
> My server crashed and when it came back it has database marked as suspect,
> I
> detached the database , delete the log file and try to attach it again
> using
> enterprise manager however it saying it can't do it.
> I have no backup of log or database for the day our backup was also not
> working.
> What I can do to recover the database.
>
> Thanks for the help
> Tanweer

Cyclical reference problem with 2 "on delete cascades" pointing to same table.

I am not sure if this is necessarily a simple question, but I'm somewhat new to SQL so I thought maybe there's an obvious answer I just don't know about.

The problem is that I have one "master" table, and a child table that has two foreign key references back to that master table. Both of these foreign key constraints are marked as "on delete cascade" with the intention that should a row from the master table be deleted, any rows that reference that object in EITHER foreign key field should be deleted.

I am wondering why this causes a cycle. It seems logical enough to me, it just involves two passes of the table, one for each affected column.

Thanks,
Logan

There can be only ONE column in a PK-FK link. If your design requires two columns in the child to refer to the Parent, something is amiss and should be revisited. A child can have only ONE Parent. (I know, it's closer to the 'real world' model than we would prefer, but ... ) If two columns in the Child refer to the same row in the Parent, again, there is something specious in the design.

If the row is deleted in the Parent table, it really doesn't matter what column is linked to the Child table, with CASCADE DELETE, rows in the Child table with a PK_FK link will be deleted.